A check constraint is a type of constraint that is used in SQL to ensure that a specified condition is met before data is added or modified in a table. Check constraints are used to enforce business rules or logic on a table column or a set of columns.
A check constraint is defined as part of the table schema, and it is created using the ALTER TABLE statement.
The syntax for creating a check constraint is as follows:
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);
The table_name parameter specifies the name of the table on which the constraint is being applied. The constraint_name parameter is a user-defined name for the constraint, and the condition parameter is the logical expression that defines the constraint.
For example, let’s consider a table named Employees that has a column named Age. We can add a check constraint to ensure that the age of the employees is greater than or equal to 18, using the following SQL statement:
ALTER TABLE Employees ADD CONSTRAINT chk_Employees_Age CHECK (Age >= 18);
Once the check constraint is added, any attempt to insert or update a row in the Employees table with an age less than 18 will result in an error.
CREATE TABLE Training_Course ( ID INT PRIMARY KEY, NAME VARCHAR(250) NOT NULL, DURATION INT, PRICE INT ); ALTER TABLE Training_Course ADD CONSTRAINT CHK_TC CHECK (DURATION > 4 and PRICE < 500 ); INSERT INTO Training_Course(ID, NAME, DURATION, PRICE) values(1,'SQL',5,200); (1 row(s) affected) INSERT INTO Training_Course(ID, NAME, DURATION, PRICE) values(2,'T-SQL',7,700); The INSERT statement conflicted with the CHECK constraint "CHK_TC". The conflict occurred in database "model", table "dbo.Training_Course".
Check constraints are useful in maintaining data integrity in a database. They ensure that the data stored in a table is consistent with the business rules or logic of the organization. By using check constraints, developers can prevent the insertion of invalid or inconsistent data into a table, which helps to maintain the quality of the data and reduce the risk of data errors.