PostgreSQL CHECK Constraint

Updated May 24, 2023

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 the update or insert operation if it does not meet the condition defined.

Examples

Let’s create table furniture to understand the CHECK constraint in detail.

1. PostgreSQL CHECK constraint with the default name

Let’s create a table named ‘furniture’ 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);

After executing the above statement, we will get the following error as the funiture_price can not insert a negative value. Consider the following snapshot to illustrate the result.

So, the above insert statement is failed as we tried to insert a negative value in the furniture_price column of the ‘furniture’ table.

Now we will insert the positive value into 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 the ‘furniture’ table with the help of the following SQL statement and snapshot:

select * from furniture;

The name of the PostgreSQL CHECK constraint defaults with the following pattern:

{table}_{column}_check

Like, the table furniture has the constraint on the 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

As shown above, we need to specify the constraint name after the CONSTRAINT keyword.

Let’s create a table named ‘furniture’ 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);

After executing the above statement, we will get the following error as the funiture_price can not insert a negative value. Consider the following snapshot and verify the CONSTRAINT name as ‘positive_furniture_price’.

Now we will insert the positive value into 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 successfully insert the record in the furniture table as the price is positive.

Let’s check the status of the ‘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’ 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 the 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 point 3 above on the furniture_price table with the help of the following statement:

ALTER TABLE furniture DROP CONSTRAINT positive_furniture_check Now, the table does not have any CHECK CONSTRAINT assigned for any column so that we can insert positive or negative values in the furniture table.

Let’s verify the INSERTION using the 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

From the above article, we hope you understand 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

We hope that this EDUCBA information on “PostgreSQL CHECK Constraint” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Database
2. Install PostgreSQL
3. SQL Left Join
4. Natural Join SQL