PostgreSQL Constraints
2021-06-16 13:05:44

PostgreSQL Constraints

PostgreSQL Constraints

PostgreSQL Constraints can be defined as the rules that are enforced on the columns of a table. These rules are used to keep a check on the data that is entered in the columns on which those constraints are defined. These PostgreSQL Constraints help us to maintain the accuracy and the reliability of the data in a database.

We can define Constraints either for a column or for the whole table, which will depend upon the need of the database. The column level constraint will only have a check on that particular column whereas the table constraint will be applicable to the whole table.

The constraints provided by PostgreSQL are most frequently used in such scenarios:

  • Once a constraint is created. The constraint is then linked to a table and the linked constraint can be disabled.
  • The column constraints can be used for the individual columns in a table.
  • The constraints can be declared at the time of the creation of a table using the CREATE table command.
  • The values which interrupt the well-defined criteria of a constraint are discarded immediately.
  • A data dictionary is maintained to store all the logs or information linked to the constraints.
  • We can use the table constraints to one or more columns.

Types of PostgreSQL Constraints:

The various types of Constraints supported by PostgreSQL are:

  • Not Null Constraint: This type of constraint is used when we do not want a null value. The column defined with Not Null Constraint cannot remain empty.
  • CheckItConstraint: The CheckIt Constraint is used when we want to satisfy certain conditions such as it must match a Boolean expression to ensure that every value in that column or a field of a table satisfies that condition.
  • UniqueConstraint: The unique constraints are helpful in ensuring that data present in a column of a table are unique or exclusive. The column defined with Unique Constraint should not have repetitive values. This constraint is helpful in reducing the duplicity of data.
  • Primary key Constraint: The primary key is used to ensure that each row or record in a table in a database is unique and make sure that there is no duplicity of the records in a particular table.
  • Foreign key Constraint:         In PostgreSQL, the foreign key constraint is used to ensure that the values present in that column are equal to the values that are in the primary key column of the other table. 
  • Exclusion Constraint:The Exclusion Constraint makes sure that the specified operators used don’t return always a TRUE value while comparing the specified columns or the expressions.

Now let us see each one of them in detail to have a better understanding of each one of them.

Unique Constraint

The unique constraints are helpful in ensuring that data present in a column of a table are unique or exclusive. The column defined with Unique Constraint should not have repetitive values. This constraint is helpful in reducing the duplicity of data.

For the creation of a Unique Constraint in PostgreSQL, the syntax defined is :





To create a table named customer having five columns and Unique Constraint on the ID column has the below-mentioned syntax:




 CREATE TABLE CUSTOMER_RECORDS(
  CUST_ID INTEGER UNIQUE,
  CUST_AGE INTEGER,
  CUST_NAME TEXT NOT NULL,
  CUST_ADDRESS CHAR (35),
  CUST_SALARY DECIMAL (16, 4),
 ); 




NOT NULL Constraint:




By default, a column in PostgreSQL is allowed to store NULL values. If we want a column to have other than the NULL value, then it is compulsory to define NOT NULL constraint on that column specifying that NULL value is now not permitted for that particular column.




A NOT NULL constraint can never be a table constraint, it is always defined as a constraint on a column.




The syntax for NOT NULL Constraint in PostgreSQL is:




 CREATE TABLE name_of_the_table(
 columnOnedata_type NOT NULL,
 columnTwodata_type NOT NULL,
 columnThreedata_type,
  …
 columnNdata_type, 
 );  




Below is the syntax for creating a table named Company and having multiple NOT NULL constraints on different columns.




 CREATE TABLE COMPANY_RECORDS(
 emp_ID                INT   PRIMARY KEY      NOT NULL,
 emp_NAME         TEXT                              NOT NULL,
 emp_AGE  INT                                  NOT NULL,
 emp_ADDRESSCHAR(50),
 emp_SALARY         REAL
 ); 




So, this article gives us a clear picture of what is PostgreSQL constraints and what are their different types.