SQL CHECK Constraint
SQL CHECK constraint is used to specify a condition that must be met for the data to be considered valid in a column. It is used to ensure that the values in a column meet a specific condition. If the condition is not met, the INSERT or UPDATE operation will be rolled back.
1CREATE TABLE table_name ( 2 column1 datatype CHECK (column_constraint), 3 column2 datatype CHECK (column_constraint), 4 ... 5);
1ALTER TABLE table_name 2ADD CONSTRAINT constraint_name CHECK (column_constraint);
Example: Let's create a
customers table with a
age column and apply the CHECK constraint that the age should be greater than or equal to 18:
1CREATE TABLE customers ( 2 CustomerID int NOT NULL, 3 CustomerName varchar(255) NOT NULL, 4 Age int CHECK (Age >= 18) 5);
Inserting data into the table:
1INSERT INTO customers (CustomerID, CustomerName, Age) 2VALUES 3 (1, 'William', 20), 4 (2, 'Jane', 30), 5 (3, 'Jim', 40);
Try to insert a record with age less than 18:
1INSERT INTO customers (CustomerID, CustomerName, Age) 2VALUES (4, 'Jack', 17);
The above statement will throw an error as the CHECK constraint is not met.
1Output: 2Msg 547, Level 16, State 0, Line 1 3The INSERT statement conflicted with the CHECK constraint "CK__customers__Age__74AE54BC". The conflict occurred in database "TestDB", table "dbo.customers", column 'Age'.
SQL CHECK Constraint on ALTER TABLE
SQL CHECK Constraints are used to specify a condition on each row of the table, that must be true for each row. It is used to enforce data integrity within a column by limiting the values that can be inserted into a table.
A CHECK Constraint can be added to a table using the ALTER TABLE statement along with the ADD CONSTRAINT clause. The CHECK Constraint can be defined using a condition in the CHECK clause, which specifies the values that can be inserted into the column.
1ALTER TABLE table_name 2ADD CONSTRAINT constraint_name CHECK (column_name condition);
For example, let's say we have a table named "Orders" with the following data:
We can add a CHECK Constraint to the "OrderAmount" column to ensure that all order amounts are greater than or equal to 50.
1ALTER TABLE Orders 2ADD CONSTRAINT chk_OrderAmount CHECK (OrderAmount >= 50);
SQL DROP a CHECK Constraint
A CHECK Constraint can also be dropped using the ALTER TABLE statement along with the DROP CONSTRAINT clause.
1ALTER TABLE table_name 2DROP CONSTRAINT constraint_name;
For example, if we want to drop the "chk_OrderAmount" CHECK Constraint from the "Orders" table, the following statement can be used:
1ALTER TABLE Orders 2DROP CONSTRAINT chk_OrderAmount;