SQL Check Constraint

Constraints refer to rules that govern how data is managed within a table column. It also help add security and maintain data integrity.

This article will discuss how to can use the CHECK constraints in SQL databases.

What is SQL Check Constraint?

The check constraint in SQL allows you to limit the data that can be added to a column. For example, using the check constraints, you can specify a Boolean expression where all the data inserted in the column must gratify.

SQL Add Check Constraint

Let us discuss how to add a check constraint when creating a table. The syntax for creating a check constraint in SQL is as shown below:

CONSTRAINT constraint_name CHECK(Boolean_expression);

Consider the example query shown below:

CREATE TABLE users (
id INT NOT NULL,
first_name VARCHAR(255),
last_name VARCHAR(255),
age INT NOT NULL,
country VARCHAR(255) NOT NULL ,
CONSTRAINT check_age CHECK(age >= 18);
);

In the example above, we create a table called users; we add various columns and a check constraint.

In this case, we tell SQL to verify that the age specified is greater than or equal to 18. This prevents data with the age value of 17 and below from being added to the database.

Note that you can specify multiple check conditions for a column.

SQL Add Check constraint to an Existing Table

You can add a check constraint to an existing table using the ALTER TABLE statement. For example, try adding a check constraint to ensure the age above 18 and below 60 as illustrated below.

ALTER TABLE users
ADD CONSTRAINT check_age CHECK (age >= 18 AND age <= 60);

Keep in mind that adding a check constraint to an existing table does not affect existing data. However, new data must fulfill the specified condition.

SQL Remove Check Constraint

To remove a check constraint from a table, we can use the DROP statement as shown in the example below:

ALTER TABLE users
DROP CONSTRAINT check_age;

In this case, we use the DROP CONSTRAINT statement followed by the name of the constraint we wish to remove.

SQL Modify Check Constraint

If you wish to modify the expression of a check constraint, you need to drop the existing constraint and create a new one.

Modification of the Boolean expression for an existing check constraint is not supported.

Conclusion

This article covered how to add a check constraint to a new table. We also covered adding a check constraint to an existing table and removing the constraint.