SQL CHECK Constraint

In SQL, the CHECK constraint is used to specify the condition that must be satisfied in order to insert data to a table. For example,

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT CHECK (amount > 0)
);

Here, the amount column has a check condition: greater than 0. Now, let's try to insert records to the Orders table.

Example 1

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

Example 2

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

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 named CHECK constraint:

-- creates a named constraint named amount CK
-- 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)
);

CHECK Constraint in Existing Table

We can add the CHECK constraint to an existing table by using the ALTER TABLE clause. For example,

-- Adding CHECK constraint without name

ALTER TABLE Orders
ADD CHECK (amount > 0);

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

-- Adding CHECK constraint named amountCK

ALTER TABLE Orders
ADD CONSTRAINT amountCK CHECK (amount > 0);

Note: If we try to add the CHECK constraint amount > 0 to a column that already has value less than 0, we will get an error.

Remove CHECK Constraint

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

SQL Server, PostgreSQL, Oracle

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

MySQL

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