What all constraints can be added to a PostgreSQL table?

There are 6 types of constraints that can be generally used with a PostgreSQL table. They are listed and explained below −

NOT NULL CONSTRAINT

This is a very common constraint. If there is a particular column that cannot have null values, you add this constraint at the time of table creation. For example, if we create a marks table, which can’t have NULL values for the name, then the table creation command will look like −

CREATE TABLE marks(
   name VARCHAR NOT NULL,
   roll_no INTEGER,
   marks_obtained INTEGER
);

Now, if we try to insert a row into this table, without the name column value, we will see an error. Let us try −

INSERT INTO marks(roll_no, marks_obtained)
VALUES(25,56)

The error you will get will be similar to the following −

ERROR: null value in column "name" violates not-null constraint DETAIL: Failing row
contains (null, 25, 56). SQL state: 23502

UNIQUE CONSTRAINT

As the name suggests, this constraint implies that the concerned column cannot have duplicate values. This again, is a very common constraint. If we now create the marks table and want to set the UNIQUE constraint on the roll_no column, then this is how that can be done −

CREATE TABLE marks(
   name VARCHAR,
   roll_no INTEGER UNIQUE,
   marks_obtained INTEGER
);

Now, if we try to insert duplicate roll number values, PostgreSQL will throw up an error. Let us try −

INSERT INTO marks(name, roll_no, marks_obtained)
VALUES('Yash',25,56),
('Isha',25,64)

The error you will get will be similar to the following −

ERROR: duplicate key value violates unique constraint "marks_roll_no_key" DETAIL: Key
(roll_no)=(25) already exists. SQL state: 23505

PRIMARY KEY CONSTRAINT

This is a combination of NOT NULL and UNIQUE constraints. This can be applied either on a single column or on multiple columns. If PRIMARY KEY is applied on multiple columns, then the combination of those columns should have unique values, whereas the individual columns can have repeated values.

Let us see an example of applying the PRIMARY KEY constraint on one column and on multiple columns −

Single Column −

CREATE TABLE marks(
   name VARCHAR,
   roll_no INTEGER PRIMARY KEY,
   marks_obtained INTEGER
);

Multiple Columns −

CREATE TABLE marks(
   name VARCHAR,
   roll_no INTEGER,
   marks_obtained INTEGER,
   PRIMARY KEY (name, roll_no)
);

In the case of Primary Key constraint, adding either null values or duplicate values will cause PostgreSQL to throw up an error. Checking this error is left as an exercise for the reader.

FOREIGN KEY CONSTRAINT

This constraint helps maintain consistency between multiple tables. Say you already have a marks table created in the previous example, which has roll_no as the PRIMARY KEY. Now, if you are creating another table called student_info, which also has a roll_no column, and you want to ensure that only those values of roll_no are permitted in the student_info table that are already present in the marks table, then you will add a FOREIGN KEY constraint on the roll_no column in the student_info table. This is how you can do it −

CREATE TABLE student_info(
   name VARCHAR,
   roll_no INTEGER REFERENCES marks (roll_no),
   age INTEGER,
   gender VARCHAR
);

Now, the roll_no column of student_info references the roll_no column of the marks table. So you will not be able to add any value in student_info whose roll_no is not already present in the marks table.

If my marks table is as follows −

Then let me add try to add values in the student_info table with roll_no 45.

INSERT INTO student_info(name, roll_no, age, gender)
VALUES ('Siddhi',45,23,'F');

This query succeeds because the roll_no 45 exists in the marks table. Now, let us try to add roll_no 12 in the student_info table.

INSERT INTO student_info(name, roll_no, age, gender)
VALUES ('Aniket',12,26,'M');

PostgreSQL will throw up an error here −

ERROR: insert or update on table "student_info" violates foreign key constraint "student_info_roll_no_fkey" DETAIL: Key (roll_no)=(12) is not present in table "marks". SQL state: 23503

Just like we can add a PRIMARY KEY constraint on multiple columns, we can also add a FOREIGN KEY constraint on multiple columns. An example is given below −

CREATE TABLE student_info(
name VARCHAR,
roll_no INTEGER,
age INTEGER,
gender VARCHAR,
FOREIGN KEY (name, roll_no) REFERENCES marks (name, roll_no)
);

CHECK CONSTRAINT

This is a custom constraint. It allows us to force a column to satisfy a Boolean expression. For example, if we want the roll_no to be greater than 0 in the marks table, we can add that constraint as follows −

CREATE TABLE marks(
   name VARCHAR,
   roll_no INTEGER CHECK (roll_no > 0),
   marks_obtained INTEGER
);

Now, if I try to add an entry with roll_no =0 in this table, I’ll see the following error −

INSERT INTO marks(name, roll_no, marks_obtained)
VALUES('Yash',0,25)

ERROR: new row for relation "marks" violates check constraint "marks_roll_no_check"
DETAIL: Failing row contains (Yash, 0, 25). SQL state: 23514

EXCLUSION CONSTRAINT

The explanation of this constraint is nailed by a Stack Overflow answer. You can check it out here − https://stackoverflow.com/a/51247705.Now, this is a more generic extension of the UNIQUE CONSTRAINT. UNIQUE says no two rows can have the same value for the concerned column. This constraint takes it a step further. It says, no two rows should _______. You can fill anything in the blank space. You can say “No two rows can be different”, or “No two rows can intersect” or any other statement, depending on the context and the column being considered. Now, the UNIQUE constraint is a special case of the EXCLUSION CONSTRAINT. But UNIQUE is simple. It just says no two rows can be the same. So, we essentially check for the = operator. And the = checks are easy to perform. But not all operator checks will be as easy. Therefore, the EXCLUSION constraint requires you to create an index on the column on which you are applying the constraint. This will ensure that the comparisons are fast. Let us understand this with an example.

CREATE TABLE marks(
   name VARCHAR,
   roll_no INTEGER,
   marks_obtained INTEGER,
   EXCLUDE USING gist(roll_no WITH <>)
);

Note that you must have run CREATE EXTENSION btree_gist once per database, for the above command to work.

Now, <> is the not equal operator in PostgreSQL. What we are essentially telling PostgreSQL is that for every row that is added to the table, check the roll_no with every other roll_no already present, using the NOT EQUAL operator. Allow it only if all comparisons give the result FALSE. In other words, only allow all the roll numbers to be the same, don’t allow different roll numbers.

Now, let us add one value to the above table −

INSERT INTO marks8(name, roll_no, marks_obtained)
VALUES('Yash',26,55)

This will work. Now let us add another value with a different roll_no.

INSERT INTO marks8(name, roll_no, marks_obtained)
VALUES('Isha',56,65)

This will throw an error −

ERROR: conflicting key value violates exclusion constraint "marks_roll_no_excl"
DETAIL: Key (roll_no)=(56) conflicts with existing key (roll_no)=(26). SQL state:
23P01

If I wanted to enforce the UNIQUE constraint using EXCLUSION, I would have replaced <> with =.

You can also add the EXCLUDE CONSTRAINT on multiple columns. An example is given here −https://www.tutorialspoint.com/postgresql/postgresql_constraints.htm#:~:text=EXCLUSION%20Constraint.

Yash Sanghvi

Updated on: 02-Feb-2021

157 Views

Kickstart Your Career

Get certified by completing the course

Get Started