Skip to main content

Constraints in PostgreSQL


Constraints are some predefined rules applied to columns that are followed to maintain the accuracy and reliability of data. Data Type is self a constraint in PostgreSQL, you can't put string data in INT data type column. The constraints applied to the columns while creating the table, means constraint create while creating a table. Constraints can be on column level or table level, column level constraint applied on particular column and table level constraint applied on whole table.
Constraints available in PostgreSQL are described below.
1- Primary Key Constraint
2- Unique Key Constraint
3- Not Null Constraint
4- Check Constraint
5- Foreign Key Constraint
6- Exclusion Constraints

1- Primary Key Constraint

We use the primary key constraint on the column to uniquely identify the row in a database table. It can't be null and every value of the column should be unique. The primary key has a crucial role in database schema design, we use them to make relation from one table to another table, and it becomes a foreign key in other tables.

Example of Primary Key Constraint

We will create a table Employee table which Id column has Primary Key. Use the following query to create Employee table.

CREATE TABLE public.employee
(
    id serial NOT NULL,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id)
)


CONSTRAINT employee_primarykey PRIMARY KEY (id) statement is use to create primary key constraint.
Note: A primary key has one or more columns but a table can have only one primary key. When more than one column is used to create a primary key, it is called a composite key.

2- Unique Key Constraint

The unique key constraint ensures that there should not be the same value for a particular column. It prevents for the duplicate value for a column, it works much more like Primary Key but it can have one null value while Primary Key can't have the null value, for the more update about the difference between Primary key and Unique key the link.

Example of Unique Key Constraint

Suppose we have an employee table and the column UserName should have the unique values, for this scenario we will use Unique Key constraint for the column UserName.

CREATE TABLE public.employee
(
    id serial NOT NULL,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
          CONSTRAINT "uk_employee_UserName" UNIQUE (username)
)


CONSTRAINT "uk_employee_UserName" UNIQUE (username) statement creates the unique key constraint.

3- Not Null Constraint

Not Null Constraint ensures that there should not be the null value for the particular column. If you don't assign Not Null Constraint to the column it by default holds the null value. If you don't want a particular column have the null value then you can use the Not Null Constraint.

Example of Not Null Constraints

In employee table, if we don't want the column Salary to hold null value then we should use Not Null constraint for this column.

CREATE TABLE public.employee
(
    id serial NOT NULL,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
    CONSTRAINT "uk_employee_UserName" UNIQUE (username)
)


4- Check Constraint

Sometimes we need to save data with the condition in the table for a particular column, if any condition is applied to the column then the check constants ensure that the particular value being entered into a table column must follow the condition. If the condition is violated by the value being entered in a table, then the record violates the constraint and is not inserted in the table.

Example of Check Constraint

As per the Child labor act of the Government of India, no organization can hire a person below the age of 18 years. So if we are creating a employee table, the Age column should have a condition, the value being inserted must be greater than 18.

CREATE TABLE public.employee
(
    id serial NOT NULL,,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid bigint,
    age integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
    CONSTRAINT "uk_UserName" UNIQUE (username),
    CONSTRAINT chk_employee_age CHECK (age > 18)
)


5- Foreign Key Constraint

Foreign Key Constraint maintains the referential integrity between two tables. It specifies that the value in a column (or a group of columns) must match the values appearing in some row of another table.

Example of Foreign Key Constraint

Suppose have an employee table and employee's address table, address table have reference of employee table.
Employee table

CREATE TABLE public.employee
(
    id serial NOT NULL,-- This is primary key
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
          CONSTRAINT "uk_employee_UserName" UNIQUE (username)
)

Address table has reference of Employee table(empid)

CREATE TABLE public.Address
(
          id serial NOT NULL,
          empid integer REFERENCES public.employee (id), --foreign key
          address1 text,
          address2 text,
          city text,
          country text
)


6- Exclusion Constraints

Exclusion Constraint is not the most common type of constants we use it rarely in database table schema, but sometimes it may be important in a particular scenario. It ensures that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. After reading the definition confused? Let me explain, suppose we design schema for an institute to manage the trainee data such as subject, class time, daily progress report, etc. Now, suppose you are assigning the class time to the student, it may be possible for the same student, the user can assign two classes at the same time. To resolve that types of issue we use Exclusion Constraint.

Example of Exclusion Constraint

I am creating student subject period assigning table(Table name is Period).

CREATE TABLE ClassPeriod
(
          id serial not null,
          stdid integer not null,
          timeperiod tsrange,
          EXCLUDE USING gist (
           stdid WITH =,
           timeperiod WITH &&
          )
);


Now, try to insert some records

INSERT INTO ClassPeriod(stdid,timeperiod)
          VALUES(1,'[2019-01-01 14:30, 2019-01-01 15:30)');
         
INSERT INTO ClassPeriod(stdid,timeperiod)
          VALUES(1,'[2019-01-01 16:30, 2019-01-01 17:30)');
         
INSERT INTO ClassPeriod(stdid,timeperiod)
          VALUES(1,'[2019-01-01 14:30, 2019-01-01 15:30)');


First and second insert query will run without error because for the same student there are different time period is assigned, while third insert query will return the error as below.
ERROR:  conflicting key value violates exclusion constraint "classperiod_stdid_timeperiod_excl"
DETAIL:  Key (stdid, timeperiod)=(1, ["2019-01-01 14:30:00","2019-01-01 15:30:00")) conflicts with existing key (stdid, timeperiod)=(1, ["2019-01-01 14:30:00","2019-01-01 15:30:00")).
SQL state: 23P01
The student and Time range of third insert query same as first so it creates the violation exclusion constraint.
Note: Please execute the command CREATE EXTENSION btree_gist, for database. This query will install the btree_gist extension, which defines the exclusion constraints on plain scalar data types.