Day 39 – SQL CHECK Constraint
In SQL, a CHECK constraint is used to ensure that the values in a column meet a specified condition or set of conditions. The CHECK constraint is defined at the time of creating the table or added later using the ALTER TABLE statement.
The syntax for defining a CHECK constraint when creating a table is as follows:
CREATE TABLE table_name (
column1 datatype CHECK (condition),
column2 datatype,
column3 datatype,
.....
);
In the above syntax, column1 is the name of the column on which the CHECK constraint is defined, datatype is the data type of the column, and condition is the expression that must be evaluated to TRUE for the value to be inserted or updated in the column.
The syntax for adding a CHECK constraint to an existing table is as follows:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);
In the above syntax, constraint_name is the name of the CHECK constraint, and condition is the expression that must be evaluated to TRUE for the value to be inserted or updated in the column.
For example, let’s say we have a table named “employees” with a column “age”, and we want to ensure that the age of an employee is not less than 18 and not greater than 60. We can define a CHECK constraint on the age column as follows:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT CHECK (age >= 18 AND age <= 60),
salary DECIMAL(10,2)
);
In the above example, we have defined a CHECK constraint on the age column to ensure that the age is between 18 and 60 (inclusive).
When a CHECK constraint is violated, the database will not allow the insertion or update of data in the column that violates the constraint.
