SQL CHECK Constraint is an important aspect of relational database management systems like MySQL, SQL Server, Oracle, and MS Access. It is used to impose restrictions on the data that can be inserted into a table, thereby ensuring data integrity and consistency.
What is SQL CHECK Constraint?
A CHECK constraint is a type of validation rule that specifies the values that are allowed in a column. When you add a CHECK constraint to a table, the database management system verifies that the values in the specified column meet the specified condition before inserting or updating the data in the table.
For example, if you want to ensure that the values in a “Age” column are greater than 18, you can add a CHECK constraint to the “Age” column with the condition “Age > 18”.
Syntax for SQL CHECK Constraint
The syntax for creating a CHECK constraint is different for each database management system. Here is the syntax for creating a CHECK constraint in different relational database management systems:
MySQL
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... CHECK (column_name condition) );
SQL Server
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);
Oracle
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);
MS Access
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);
In the above syntax,
table_name
is the name of the table to which the CHECK constraint is to be added.column1, column2, ...
are the columns of the table.datatype
is the data type of the columns.column_name
is the name of the column to which the CHECK constraint is to be added.condition
is the condition that the values in the column must meet.constraint_name
is the name of the CHECK constraint.
Examples of SQL CHECK Constraint
Here are a few examples of how to use CHECK constraints in different relational database management systems:
MySQL
CREATE TABLE employees ( id int, name varchar(255), age int, CHECK (age > 18) );
In this example, a table named “employees” is created with three columns: “id”, “name”, and “age”. A CHECK constraint is added to the “age” column with the condition “age > 18”, which means that only values greater than 18 can be inserted in the “age” column.
SQL Server
ALTER TABLE employees ADD CONSTRAINT check_age CHECK (age > 18);
In this example, a CHECK constraint named “check_age” is added to the “employees” table with the condition “age > 18”.
Oracle
ALTER TABLE employees ADD CONSTRAINT check_age CHECK (age > 18);
In this example, a CHECK constraint named “check_age” is added to the “employees” table with the condition “age > 18”.
MS Access
ALTER TABLE employees ADD CONSTRAINT check_age CHECK (age > 18);
In this example, a CHECK constraint named “check_age” is added to the “employees” table with the condition “age > 18”.
Conclusion
SQL CHECK Constraint is a useful feature of relational database management systems that allows you to enforce data integrity and consistency. It ensures that only valid data is inserted into a table by specifying conditions that the data in a column must meet. By using CHECK constraints, you can avoid data errors and improve the quality of your data. If you are new to SQL CHECK Constraint, I hope this guide has helped you understand how it works and how to use it in different relational database management systems.