PostgreSQL CHECK Constraint

In this section, we are going to understand the working of the PostgreSQL Check constraint, which is used to make sure that all values in a column or a field of a table satisfy particular situations.

The examples of the PostgreSQL check constraint, how to create a check constraint for new tables and existing tables.

What is PostgreSQL CHECK Constraint?

In PostgreSQL, the Check constraint can be defined by a separate name. It is used to control the value of columns being inserted.

It allows us to verify a condition that the value being stored into a record. If the statement is false, then the data disrupts the constraint which is not saved in the table.

In other words, we can say that the PostgreSQL CHECK constraint is used to make sure that all values in a column or a field of a table satisfy certain conditions.

For example, the check constraints must match a Boolean expression to calculate the values before they are inserted or updated to the column.

PostgreSQL will update or insert these values to the column if the values keep the check constraint. Or else, PostgreSQL will discard the modification and raise a constraint violation error.

The syntax for creating a CHECK Constraint using a create table statement

The below illustrations are used to display how we can create a CHECK Constraint using the CREATE TABLE command:

CREATE TABLE table_name(    

col1 datatype,    

col2 datatype,   

CONSTRAINT constraint_name  

CHECK(condition);  

);

Description of Parameters

In the above syntax, we have used the following parameters, which we discussed in the below table:

Parameter Name	Description
Table_name	It is the name of a table which we are going to create.
column1, column2	These are columns that we created in the table.
constraint_name	The constraint_name parameter is used to specify the name of the CHECK Constraint.
Column_name(s)	These are the columns that are going to be a CHECK Constraint.

Example of PostgreSQL CHECK Constraint

To understand the PostgreSQ CHECK Constraint’s working, we will see the below example.

When we are specifying the table’s structure using the CREATE TABLE command, we can generally use the CHECK constraint.

In the below example, we create a new table called Worker, which contains multiple columns, such as Worker_ID, Worker_name, DOB, Joining_date, and Salary.

But, before creating the Worker table, we will use the DROP TABLE command to check whether a similar table is already existing in the Jtp database.

DROP TABLE IF EXISTS Worker;  

Output

After executing the above command, we will get the following window message: The Worker table does not exist.

Now, we will create the Worker table using the CREATE command, as shown in the following statement:

CREATE TABLE Worker (  

Worker_id SERIAL PRIMARY KEY,  

Worker_name VARCHAR (40),  

DOB DATE CHECK (DOB> '1900-01-01'),  

    Joined_date DATE CHECK (joined_date >DOB),  

    Salary numeric CHECK(salary > 0)  

);

Output

On executing the above command, we will get the below message window, which displays that the Worker table has been created successfully into the jtp database.

In the above Worker table, we have used the following three CHECK constraints:

Check constraints	Description
First constraint	The Date of birth (DOB) of the Worker must be greater than 01/01/1900. If we try to insert the Date of birth before 01/01/1900, we will get an error message.
Second constraint	The joined_date must be greater than the date of birth (DOB). And this particular check will prevent from modifying incorrect dates regarding their semantic meanings.
Third constraint	The salary must be larger than zero.

After creating the Worker successfully, we will enter some values into it with the INSERT command’s help.

INSERT INTO Worker (Worker_name, DOB, Joined_date, Salary)  

VALUES ('Mike Ross', '1985-10-01', '2018-11-23', - 300000); 

Output

After implementing the above command, PostgreSQL issued the below error because we inserted a negative salary value (-300000) into the Salary column.

ERROR: new row for relation “worker” violates check constraint “worker_salary_check” DETAIL: Failing row contains (2, Mike Ross, 1985-10-01, 2018-11-23, -300000).

And the insert command is failed due to the CHECK constraint on the Salary column, which takes only positive values.

By default, PostgreSQL provides the CHECK constraint a name with the help of the below pattern:

{table}_{column}_check  

Let us see an example where the constraint on the Salary column has the below constraint name:

Worker_salary_check  

PostgreSQL CHECK constraint in a separate name

If we want to allocate the CHECK constraint a separate name, we can define it after the CONSTRAINT expression, as we can see in the following illustration:

column_name data_type CONSTRAINT constraint_name CHECK(...)  

Let us see one sample example to understand how we can use the separate name in a PostgreSQL Check constraint:

For this, we will create one new table as Orders using the CREATE command, as shown below:

CREATE TABLE Orders(  

Order_no INTEGER,  

Article_name VARCHAR(45),  

Order_date DATE,  

Order_quantity NUMERIC,  

Article_Price NUMERIC CONSTRAINT positive_Article_price CHECK (Article_Price>0)  

);

Output

On executing the above command, we will get the following window message, which displays that the Orders table has been created successfully.

If we want to see the constraint data dictionary, we can follow the below steps:

Step 1

Open the SQL shell (psql), which appeared with the necessary details. After that, we will log in to the Postgres database server using the password that we created during PostgreSQL’s installation process.

After providing the password, we are connected to the Postgres server, as we can see in the below screenshot:

Step 2

Now, we will connect to the specific database server jtp, which we created earlier with the help of the below command:

\c jtp  

Step 3

After that, we will execute the below syntax to list all constraints (Primary key, check, unique) of the particular table.

\d+ tablename  

Finally, we are using the above command to see the constraints used for the Orders table:

\d+ Orders  

Output

After implementing the above command, we will get the below result:

Note: In the above Orders table, the Article_price column can not contain any value less than or equal to zero (0).

Here, a constraint name has been included so that the keyword CONSTRAINT is followed by the named constraint positive_Article_price followed by the expression.

Define PostgreSQL CHECK constraints for existing tables using an ALTER TABLE command

In PostgreSQL, we can add the PostgreSQL check constraint for the existing table with the ALTER TABLE command’s help.

Assume that we have an existing table in the database, which is named as Amount_list.

CREATE TABLE Amount_list (  

    ID serial PRIMARY KEY,  

    Item_id INT NOT NULL,  

    Amount NUMERIC NOT NULL,  

    Discount NUMERIC NOT NULL,  

    Valid_from DATE NOT NULL,  

    Valid_to DATE NOT NULL  

);

Output

We will get the below message window after implementing the above command, which displays that the Amount_list table has been created successfully.

To add the CHECK constraints to the Amount_list table, we can use the ALTER TABLE command, where the Amount and Discount more than zero, and the discount is less than the amount.

Note: We can also use a Boolean expression, which include the AND operators.

ALTER TABLE Amount_list   

ADD CONSTRAINT Amount_Discount_check   

CHECK (  

    Amount> 0  

    AND Discount >= 0  

    AND Amount > Discount  

);

Output

After implementing the above command, we will get the below message window, which displays that the Amount_list table has been created successfully.

And the Valid_to column values must be equal or greater to the Valid_from column values, as shown in the following command:

ALTER TABLE Amount_list   

ADD CONSTRAINT valid_range_check   

CHECK (Valid_to >= Valid_from);

Output

On implementing the above command, we will retrieve the following message window, which displays that the specified table has been modified successfully.

Note

• The PostgreSQL Check constraints are very beneficial if we place the additional logic to restrict values.
• And we can also ensure that the data is efficient to the database correctly with the help of the CHECK constraint.

Overview

In the PostgreSQL Check Constraint section, we have learned the following topics:

• We have learned that the PostgreSQL CHECK constraint is used to check columns’ values based on a Boolean expression.
• We have used the CREATE TABLE command to create a Check constraint for the particular table.
• We can also modify the PostgreSQL check constraint using the ALTER TABLE
• We added a CHECK CONSTRAINT to the existing table with the ALTER TABLE command’s help.
• And we also understood the concept of using the PostgreSQL Check constraint with a separate name.