Summary: in this tutorial, we will introduce you to check constraints to constrain the value of columns in the table based on a Boolean expression.
A CHECK constraint is a kind of constraint that allows you to specify if a value in a column must meet a specific requirement. The CHECK constraint uses a Boolean expression to evaluate the values of a column. If the values of the column pass the check, PostgreSQL will insert or update those values.
Define PostgreSQL CHECK constraint for new tables
We normally use CHECK constraint when we define a new table using CREATE TABLE statement. The following statement defines an employees table.
1 2 3 4 5 6 7 8 | CREATE TABLE employees ( id serial PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), birth_date DATE CHECK (birth_date > '1900-01-01'), joined_date DATE CHECK (joined_date > birth_date), salary numeric CHECK(salary > 0) ); |
There are three CHECK constraints in the employees table:
- First, the birth date (
birth_date) of the employee must be greater than01/01/1900. If you try to insert someone with birth date before01/01/1900, you will receive an error message. - Second, the joined date (
joined_date) must be greater than the birth date (birth_date). This check will prevent from updating invalid dates in terms of their semantic meanings. - Third, the salary must be greater than zero, which is obvious.
Let’s try to insert a new row into the employees table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | INSERT INTO employees ( first_name, last_name, birth_date, joined_date, salary ) VALUES ( 'John', 'Doe', '1972-01-01', '2015-07-01', -100000 ) |
We have been trying to add a new employee with the negative salary. PostgreSQL returned the following error message:
1 2 | [Err] ERROR: new row for relation "employees" violates check constraint "employees_salary_check" DETAIL: Failing row contains (1, John, Doe, 1972-01-01, 2015-07-01, -100000). |
By default, PostgreSQL gives the CHECK constraint a name with using the following pattern:
1 | {table}_{column}_check |
In case you want to assign the CHECK constraint a specific name, you can specify it after the CONSTRAINT expression as follows:
1 2 3 | ... salary numeric CONSTRAINT positive_salary CHECK(salary > 0), ... |
Define PostgreSQL CHECK constraints for existing tables
To add CHECK constraints to existing tables, you use the ALTER TABLE statement. Suppose, we have an existing table in the database named prices_list
1 2 3 4 5 6 7 8 | CREATE TABLE prices_list ( id serial PRIMARY KEY, product_id INT NOT NULL, price NUMERIC NOT NULL, discount NUMERIC NOT NULL, valid_from DATE NOT NULL, valid_to DATE NOT NULL ); |
Now, we can use ALTER TABLE statement to add the CHECK constraints to the prices_list table. Price must be greater than zero. And discount must be greater than zero. And discount is less than the price. Notice that we used a Boolean expression that contains an AND operator.
1 2 3 4 5 | ALTER TABLE prices_list ADD CONSTRAINT price_discount_check CHECK ( price > 0 AND discount >= 0 AND price > discount ); |
The valid to date ( valid_to) must be greater than or equal to valid from date ( valid_from).
1 2 | ALTER TABLE prices_list ADD CONSTRAINT valid_range_check CHECK (valid_to >= valid_from); |
The CHECK constraints are very useful to place additional logic to restrict values that the columns can accept at the database layer. By using the CHECK constraint, we can make sure that data is updated to the database is much more clean.
In this tutorial, we have shown you how to use PostgreSQL CHECK constraint to check the values of columns based on a Boolean expression.