SQL CHECK Constraint – Tutorial with Examples

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.

Leave a Reply

Your email address will not be published. Required fields are marked *