SQL CHECK Constraint

-- apply the CHECK constraint to the amount column
CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT CHECK (amount > 0)
);

CHECK Constraint Syntax

The syntax of the SQL CHECK constraint is:

CREATE TABLE table_name (
  column_name data_type CHECK(condition)
);

Here,

• table_name is the name of the table to be created
• column_name is the name of the column where the constraint is to be implemented
• data_type is the data type of the column such as INT, VARCHAR, etc.
• condition is the condition that needs to be checked

Note: The CHECK constraint is used to validate data while insertion only. To check if the row exists or not, visit SQL EXISTS.

Example 1: SQL CHECK Constraint Success

-- apply the CHECK constraint to the amount column
CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT CHECK (amount > 0)
);

-- amount equal to 100
-- record is inserted
INSERT INTO Orders(amount) VALUES(100);

Here, we have created a table named Orders with a CHECK constraint that requires the amount value to be greater than 0.

When trying to insert a record with an amount value of 100, the insertion process was successful because the value satisfies the CHECK constraint condition.

Example 2: SQL CHECK Constraint Failure

-- apply the CHECK constraint to the amount column
CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT CHECK (amount > 0)
);

-- amount equal to -5
-- results in an error
INSERT INTO Orders(amount) VALUES(-5);

Here, we have created a table named Orders with a CHECK constraint that requires the amount value to be greater than 0.

When trying to insert a record with an amount value of -5, the insertion process failed because the value doesn't satisfy the CHECK constraint condition.

Create Named CHECK Constraint

It's a good practice to create named constraints so that it is easier to alter and drop constraints.

Here's an example to create a named CHECK constraint:

-- create a named constraint named amountCK
-- the constraint makes sure that amount is greater than 0
CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT,
  CONSTRAINT amountCK CHECK (amount > 0)
);

Here, amountCK is the name given to the CHECK constraint.

CHECK Constraint in Existing Table

We can add the CHECK constraint to an existing table by using the ALTER TABLE clause. For example, let's add the CHECK constraint to the amount column of an existing Orders table.

-- add CHECK constraint without name
ALTER TABLE Orders
ADD CHECK (amount > 0);

Here's how we can add a named CHECK constraint. For example,

-- add CHECK constraint named amountCK
ALTER TABLE Orders
ADD CONSTRAINT amountCK CHECK (amount > 0);

Remove CHECK Constraint

We can remove the CHECK constraint using the DROP clause. For example,

SQL Server, PostgreSQL, Oracle

-- remove CHECK constraint named amountCK
ALTER TABLE Orders
DROP CONSTRAINT amountCK;

MySQL

-- remove CHECK constraint named amountCK
ALTER TABLE Orders
DROP CHECK amountCK;

Recommended Reading: SQL Constraints