PostgreSQL Check Constraint

In PostgreSQL, the check constraint allows you to specify a boolean condition on one or more columns which must be satisfy before inserting or updating values. Check constraints are very useful for adding additional logic or restriction at the database layer.

On every insert and update to the column, PostgreSQL uses the boolean expression to evaluate the new values. If the value passes the check, then it allows insert or update the value to a column; otherwise, it rejects the changes and raises the check constraint violation error.

Define CHECK Constraint in CREAT TABLE

One or more check constraints can be defined while creating a new table or on existing table. It can be defined at the table level or with individual column.

The following creates a check constraint on the gender column of the employee table.

CREATE TABLE employee(
    emp_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender CHAR(1) check(gender in ('F','M')),
    birthdate DATE
);

In the above example, check(gender in ('F', 'M')) defines a check constraint on the gender column with a condition that gender should be either 'F' or 'M'. It will raise an error if trying to insert values other than these.

PostgreSQL creates check constraints with the default name in the <tablename>_<columnname>_check format, as shown below.

You can also specify a custom name of your check constraints using CONSTRAINT <constraint-name> check(condition) with the column, as shown below.

CREATE TABLE employee(
    emp_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender CHAR(1) CONSTRAINT gender_check check(gender in ('F','M')),
    birthdate DATE
);

Above statement will create the following check constraint:

You can also define check constraints at the table level after all column definitions. The following creates a check constraint on the gender column at the table level:

CREATE TABLE employee (
    emp_id 	INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender CHAR(1),
    birthdate DATE,
    check(gender in ('F','M'))
);

The boolean condition of the check constraint can use two or more columns, as shown below.

CREATE TABLE employee (
    emp_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender CHAR(1),
    birthdate DATE check(hiredate > birthdate),
    hiredate DATE
);

The following defines two check constraints at the table level.

CREATE TABLE employee (
    emp_id 	INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender CHAR(1),
    birthdate DATE,
    hiredate DATE,
    check(gender in ('F','M')),
    CONSTRAINT birthdate_hiredate_check check(hiredate > birthdate)
);

The above statement will create two check constraints. check(gender in ('F','M')) will create check constraint with the default naming convention andCONSTRAINT birthdate_hiredate_check check(hiredate > birthdate) will create a check constraint that ensures that hiredate should be greater than the birthdate. The above statement will create the following check constraints:

Adding CHECK Constraints to Existing Tables

Check constraints can be added to an existing table also, with or without data, using ALTER TABLE statement, provided the column being added to check constraint satisfies the CHECK constraint criteria.

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> CHECK (<condition_1>, <condition_2>,...);

The following adds a CHECK constraint to the existing employee table.

ALTER TABLE employee
    ADD CONSTRAINT gender_check CHECK (gender in ('F','M'));

You can add multiple constraints using multiple ADD CONSTRAINT clauses, as shown below.

ALTER TABLE employee
    ADD CONSTRAINT gender_check CHECK (gender in ('F','M')),
    ADD CONSTRAINT birthdate_hiredate_check check(hiredate > birthdate);

Add Check Constraint using pgAdmin

You can add CHECK constraints while creating a new table or to existing tables using pgAdmin.

Expand your databases node in the left pane and then expand Schemas -> public -> Tables nodes.

Now, right-click on the table where you want to add the check constraints and click on 'Properties' in the context menu.

In the popup, go to the 'Constraints' tab and click on the 'Check' tab, as shown below.

Now, to add a check constraints, click on the + button to add a row in a grid. Again click on the edit icon to add check constraint definition, as shown below.

As you can see above, enter a check constraint name in the Name textbox.

Now, go to the Definition tab and select one or more columns where you want to apply the check constraint, as shown below.

Click on the Save button to add a check constraint on the selected columns.

In the same way, you can add check constraints to a new table using pgAdmin.