For an instructor lead, in-depth look at learning SQL click below.
Ensuring data integrity is a crucial aspect of managing databases. SQL offers several tools to aid in these processes, one of which is the CHECK constraint. The CHECK constraint is a rule enforced by SQL Server to limit the values that can be placed in a column. The constraint ensures that only data adhering to the defined criteria are inserted into the column.
What is CHECK Constraint?
The CHECK constraint enforces domain integrity by limiting the values that can be put into a column. The SQL CHECK constraint ensures that all values in a column satisfy certain conditions. As such, whenever the data in the column does not meet the condition, SQL server raises an error. The constraint is defined at the column level.
Syntax of CHECK Constraint
|
1 2 3 4 5 6 7 |
CREATE TABLE table_name ( column1 column_type CONSTRAINT constraint_name CHECK (condition), column2 column_type, ... ); |
The above code is a general syntax for creating a table with a CHECK constraint. The condition is a logical statement that returns a Boolean value. The condition must hold true for the data to be inserted into the table.
EXAMPLE of CHECK Constraint
|
1 2 3 4 5 6 7 |
CREATE TABLE Students ( ID INT PRIMARY KEY, NAME VARCHAR(50), AGE INT CONSTRAINT AGE_CHECK CHECK (AGE >=16) ); |
In the above code, the ‘Students’ table has a CHECK constraint on the ‘AGE’ column. The constraint enforces that the age of all students must be 16 years or older. If there’s an attempt to enter an age less than 16, SQL server will raise an error.
Adding a CHECK constraint to a table
If you have already created a table and want to add a CHECK constraint, you can do so using the following syntax:
|
1 2 3 4 5 |
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition); |
EXAMPLE:
|
1 2 3 4 5 |
ALTER TABLE Students ADD CONSTRAINT AGE_CHECK CHECK (AGE >=16); |
The above command adds a CHECK constraint to the ‘Students’ table. The constraint ensures that no half-filled records are entered.
Conclusion
Overall, the CHECK constraint is a great tool for ensuring data integrity in SQL Server. It’s simple to use and allows you to limit the data that gets into your tables, ensuring logical correctness and consistency.