Introduction to PostgreSQL CHECK constraint
We can control the value of a column(s) being inserted in the table with the help of the PostgreSQL CHECK constraint. We can define a condition to decide which value to be inserted into a table and which not with the PostgreSQL CHECK constraint. The keyword CHECK is used to define the CHECK constraint which is followed by a condition defined in the parenthesis.
Syntax
column CHECK condition
Explanation
column: the definition of the column on which we are adding check constraints.
CHECK: A keyword for adding the constraint
condition: A condition followed by the CHECK keyword for deciding to insert or update the value in the table.
How does CHECK CONSTRAINT work in PostgreSQL?
- The PostgreSQL CHECK constraint uses a boolean value returned by the condition to decide whether the value should be inserted or not.
- If a particular value satisfies the defined condition then this value is acceptable to insert into the table.
- The value will be rejected on update or insert operation if does not meet the condition defined.
Examples to Implement CHECK CONSTRAINT in PostgreSQL
Let’s create table furniture in order to understand the PostgreSQL CHECK constraint in detail.
1. PostgreSQL CHECK constraint with the default name
Let’s create a table named ‘furniture’ in order to understand the example using the CREATE TABLE statement as follows:
CREATE table furniture
(
furniture_id SERIAL PRIMARY KEY,
furniture_name VARCHAR (256) NOT null,
furniture_type VARCHAR (256) NOT null,
furniture_price int NULL CHECK(furniture_price > 0)
);
Now try to insert negative furniture prices into the furniture table.
INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',-1);
We will get the following error after executing the above statement as the funiture_price can not insert negative value. Consider the following snapshot to illustrate the result.
So, the above insert statement is failed as we tried to insert negative value in the furniture_price column of the ‘furniture’ table.
Now we will insert the positive value int the furniture price column of the ‘furniture’ table as follows:
INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',2500);
The above statement will insert the record successfully in the furniture table as the price of furniture is positive
Let’s check the status of ‘furniture’ table with the help of the following SQL statement and snapshot:
select * from furniture;
The name of PostgreSQL CHECK constraint defaults with the following pattern:
{table}_{column}_check
Like, the table furniture has the constraint on furniture_price price column which will get assigned as follows:
furniture_furniture_price_check
2. PostgreSQL CHECK constraint with an assigned name
However, we can assign the PostgreSQL CHECK constraint name as follows:
column_name data_type CONSTRAINT constraint_name CHECK condition
We need to specify the constraint name after the CONSTRAINT keyword as shown above.
Let’s create a table named ‘furniture’ in order to understand the example of assigning positive_furniture_price constraint name using the CREATE TABLE statement as follows:
CREATE table furniture
(
furniture_id SERIAL PRIMARY KEY,
furniture_name VARCHAR (256) NOT null,
furniture_type VARCHAR (256) NOT null,
furniture_price int NULL CONSTRAINT positive_furniture_price CHECK(furniture_price > 0)
);
Now try to insert negative furniture prices into the furniture table.
INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',-1);
We will get the following error after executing the above statement as the funiture_price can not insert negative value. Consider the following snapshot and verify the CONSTRAINT name as ‘positive_furniture_price’.
Now we will insert the positive value int the furniture price column of the ‘furniture’ table as follows:
INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',2500);
The above statement will insert the record successfully in the furniture table as the price of furniture is positive.
Let’s check the status of ‘furniture’ table with the help of the following SQL statement and snapshot:
select * from furniture;
3. Add CHECK constraint on an existing table
Let’s create a table named ‘furniture’ in order to understand the examples:
CREATE table furniture
(
furniture_id SERIAL PRIMARY KEY,
furniture_name VARCHAR (256) NOT null,
furniture_type VARCHAR (256) NOT null,
furniture_price int NULL
);
Now use the ALTER TABLE statement to add the constraint on furniture_price table with the help of the following statement:
ALTER TABLE furniture ADD CONSTRAINT positive_furniture_check CHECK (
furniture_price > 0
);
If the table contains any of the rows which are having negative values already present before adding CONSTRAINT on a column then we will get the following error:
4. Remove the CHECK CONSTRAINT of the table
Now use the ALTER TABLE statement to DROP the CHECK constraint added in above point 3 on furniture_price table with the help of the following statement:
ALTER TABLE furniture DROP CONSTRAINT positive_furniture_check Now the table is not having any CHECK CONSTRAINT assigned for any column, so we can insert either positive or negative values in the furniture table.
Let’s verify the INSERTION using INSERT TABLE statement as follows:
INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',-1),
('Chair','Wood',2500);
In the above example, we have inserted one positive and one negative value in the furniture table.
Illustrate the result of the above statement with the help of the following statement and snapshot
select * from furniture;
Conclusion
We hope from the above article you have understood how to use the PostgreSQL CHECK CONSTRAINT and how the PostgreSQL CHECK CONSTRAINT with condition works to insert or update values. Also, we have added some examples of PostgreSQL CHECK CONSTRAINT to understand it in detail.
Recommended Articles
This is a guide to PostgreSQL CHECK Constraint. Here we discuss the Examples to Implement CHECK CONSTRAINT in PostgreSQL. You may also have a look at the following articles to learn more –
