PostgreSQL – CHECK Constraint
In PostgreSQL, the CHECK constraint is a powerful tool used to enforce data integrity by specifying that a value in a column must meet a specific requirement. The CHECK constraint uses a Boolean expression to evaluate the values before performing an insert or update operation on the column. If the values pass the check, PostgreSQL allows the insertion or update. This constraint is primarily used while creating a table to ensure that the data adheres to the defined rules.
The CHECK constraint ensures that the data in a column meets a particular condition. This is crucial for maintaining data integrity and preventing invalid data from entering the database. By using the CHECK constraint, you can define rules that the data must comply with, such as value ranges, specific formats, or logical relationships between columns.
Syntax
variable_name Data-type CHECK(condition);
PostgreSQL CHECK Constraint Example
Now let’s jump into an example for better understanding. We will first create an employee table with specific checks as follows:
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)
);
INSERT INTO employees (
first_name,
last_name,
birth_date,
joined_date,
salary
)
VALUES
(
'Raju',
'Kumar',
'1994-01-01',
'2015-07-01',
100000
);
SELECT * FROM employee;
We can see the data inserted as below:
Now we will try to insert data that fails the check as below:
INSERT INTO employees ( first_name, last_name, birth_date, joined_date, salary ) VALUES ( 'Nikhil', 'Aggarwal', '1972-01-01', '2015-07-01', - 100000 );
Output:
ERROR: new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL: Failing row contains (2, Nikhil, Aggarwal, 1972-01-01, 2015-07-01, -100000)
Explanation: The insertion fails because the ‘salary’ value is negative, which violates the CHECK constraint that requires ‘salary’ to be greater than 0.
As we can see the behaviour is exactly what we expected.
Conclusion
The CHECK constraint in PostgreSQL is an essential tool for enforcing data integrity and ensuring that the data meets specific requirements. By understanding its syntax and applications, you can effectively use CHECK constraints to maintain high-quality data in your databases.
