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:
Description of Parameters
In the above syntax, we have used the following parameters, which we discussed in the below table:
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.
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:
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:
After creating the Worker successfully, we will enter some values into it with the INSERT command's help.
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:
Let us see an example where the constraint on the Salary column has the below constraint name:
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:
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:
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:
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:
Now, we will connect to the specific database server jtp, which we created earlier with the help of the below command:
After that, we will execute the below syntax to list all constraints (Primary key, check, unique) of the particular table.
Finally, we are using the above command to see the constraints used for the Orders table:
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.
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.
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:
On implementing the above command, we will retrieve the following message window, which displays that the specified table has been modified successfully.
In the PostgreSQL Check Constraint section, we have learned the following topics: