In SQL, the CHECK
constraint is used to specify the condition that must be validated 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);
Note: The CHECK
constraint is used to validate data while insertion only. To check if the row exists or not, visit SQL EXISTS.
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;
Recommended Readings: SQL Constraints