In SQL, the CHECK
constraint is used to specify the condition that must be validated in order to insert data into a table.
-- apply the CHECK constraint to the amount column
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
amount INT CHECK (amount > 0)
);
Here, the amount
column has a check condition: its value should be greater than 0.
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 createdcolumn_name
is the name of the column where the constraint is to be implementeddata_type
is the data type of the column such as INT
, VARCHAR
, etc.condition
is the condition that needs to be checkedNote: The CHECK
constraint is used to validate data while insertion only. To check if the row exists or not, visit SQL EXISTS.
-- 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.
-- 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.
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.
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);
Notes:
CHECK
constraint amount > 0
to a column that already has value less than 0, we will get an error.ALTER TABLE
command is not supported by our online SQL editor since it is based on SQLite.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;