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.

Syntax:

1CREATE TABLE table_name (
2   column1 datatype CHECK (column_constraint),
3   column2 datatype CHECK (column_constraint),
4   ...
5);

or

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:

OrderID	CustomerID	OrderAmount
1	10	100
2	20	200
3	30	300

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;