EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL CHECK Constraint
 

PostgreSQL CHECK Constraint

Updated May 24, 2023

PostgreSQL CHECK Constraint

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

PostgreSQL CHECK Constraint example 1

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;

PostgreSQL CHECK Constraint example 1.2

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’.

PostgreSQL CHECK Constraint example 2

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;

 example 2.2

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:

example 3

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;

example 4

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW