intro
Let’s learn everything you need to know about the SQL CHECK constraint to become a master of database data integrity.
In database management, ensuring data integrity is paramount. That is because accurate and consistent information maintains data quality and supports reliable decision-making. What is one of the most powerful tools to ensure data integrity in a database? SQL CHECK constraints! These enforce rules on column values, preventing the creation of records with invalid data.
In this guide, you will understand what CHECK SQL constraints are, how they work, and how to define them in both plain SQL and in a visual database client.
Let's dive in!
What Is a CHECK Constraint in SQL?
In SQL, a CHECK constraint is a rule applied to a column or a set of columns in a table to enforce data integrity. It ensures that all values in one or more columns meet some specific conditions. If a SQL CHECK constraint is in place in a table and an INSERT or UPDATE query violates query conditions, the whole operation is rejected.
Each database provides its own implementation of the CHECK SQL constraint. See the official documentation pages for more details:
CHECK SQL Syntax: First Examples
The CHECK SQL syntax is:
1
[CONSTRAINT [constraint_name]] CHECK (check_expression)
Where:
In general, the constraint is applied on INSERT and UPDATE statements but not on DELETE statements. Keep in mind that a single table can have several CHECK constraints.
A CHECK constraint can be:
1. A column constraint
It refers to only one column. In an ALTER TABLE query, it appears within a column definition.
Example:
1
CREATE TABLE t1(
2
c1 INT CHECK (c1 > 10),
3
-- other columns ...
4
);
2. A table constraint
A table constraint refers to one or more columns. In an ALTER TABLE query, it is usually specified at the end, after the column definition.
Example:
1
CREATE TABLE t1 (
2
c1 INT,
3
-- other columns ...
4
CHECK (c1 > 10)
5
);
The CONSTRAINT [constraint_name] part of the syntax is optional and required to define named constraints as below:
1
CREATE TABLE t1 (
2
c1 INT,
3
-- other columns ...
4
CONSTRAINT c1_positive CHECK (c1 > 10)
5
);
Otherwise, the database engine will give the CHECK constraint an automatic name, such as t1_c1_check.
Typically, SQL CHECK constraints are enforced immediately after they are defined. This means that if one or more rows do not match the rules specified in check_expression, the constraint creation will fail. In MySQL, you can avoid this automatic enforcement behavior by adding the NOT ENFORCED string at the end of the CHECK constraint definition.
Note: Avoid data type conversion in constraint definitions, as implicit or explicit data type conversion may cause certain operations to fail.
How to Deal With SQL CHECK Constraints
Now that you know the CHECK SQL syntax, you are ready to learn how to define and drop such constraints.
You will see how to perform each operation with a plain SQL query and how to do everything in DbVisualizer. If you are unfamiliar with DbVisualizer, it is a top-rated visual SQL client supporting dozens of databases. This advanced tool allows you to manage CHECK constraints in just a few clicks.
Download it for free, install it, launch it, and set up a database connection to get started.
The CHECK constraint used in the examples below ensures that salary is greater than or equal to 0 and age is greater than or equal to 18. The sample queries will be written in MySQL, but you can easily adapt them to any other SQL dialect.
Add a CHECK Constraint on CREATE TABLE
Assume you want to create a MySQL employee table while checking for salary and age validity using the aforementioned SQL CHECK constraint. This is how you can do it:
1
CREATE TABLE employee (
2
id INT AUTO_INCREMENT PRIMARY KEY,
3
fullname VARCHAR(100) NOT NULL,
4
address VARCHAR(255),
5
age INT,
6
salary DECIMAL(10, 2),
7
CONSTRAINT valid_employee CHECK (salary >= 0 AND age >= 18)
8
);
The example below will illustrate how you can do it in DbVisualizer. Connect to your MySQL server, reach your database (company, in this case) in the “Connections” section, right-click on it, and select the “Create Table…” option:
In the dialog that opens, give the table a name, define the columns, and reach the “Check Constraints” tab:
Here, click on the “+” button to specify the desired CHECK constraint:
Finally, press the “Execute” button to create the table. The result will be the same MySQL query seen before.
Find out more in the DbVisualizer documentation.
If you now try to add or update a record in the employee table with some values that do not respect the specified rule, the query will fail with this error:
1
[Code: 3819, SQL State: HY000] Check constraint 'valid_employee' is violated.
Add a CHECK Constraint with ALTER TABLE ADD CONSTRAINT
Now, suppose you want to add the CHECK constraint to an existing table. You can do this if you make use of the ALTER TABLE ... ADD CONSTRAINT statement:
1
ALTER TABLE employee
2
ADD CONSTRAINT valid_employee CHECK (salary >= 0 AND age >= 18);
In DbVisualizer, right-click on the table you want to add the constraint to, and select the “Alter Table…” option:
In the “Alter Table” modal, reach the “Check Constraint” tab, and follow the same procedure as before:
As you can see, the resulting SQL query is the same as the one presented above.
DROP a CHECK Constraint
You can remove a CHECK constraint by specifying its name in the following DROP CONSTRAINT query:
1
ALTER TABLE employee DROP CONSTRAINT valid_employee;
In DbVisualizer, reach the “Check Constraint” tab, and simply click the “-” button.
Rename a CHECK Constraint
In most databases, it is not possible to rename a constraint. Instead, you must delete it and recreate it with a different name.
PostgreSQL and Oracle allow that via the RENAME CONSTRAINT query:
1
ALTER TABLE company RENAME CONSTRAINT valid_employee TO chk_employee
In DbVisualizer, you can change the name of a constraint by double-clicking on it in the “Check Constraint” tab.
Popular Use Cases
These are some common use cases for CHECK:
Conclusion
In this article, you learned what the SQL CHECK constraint is, which databases support it, and how it helps define data integrity rules. Thanks to the use cases explored here, you now understand when to apply it in real-world applications.
As demonstrated here, defining constraints is much easier with DbVisualizer. Thanks to its intuitive UI, this database client allows you to create, update, and delete constraints with just a few clicks. These are only some of the features this powerful tool supports: DbVisualizer supports many other features such as query optimization and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
How to update a CHECK constraint?
No, it is not possible to update an existing CHECK constraint in SQL. To do so, you need to drop the constraint and then add a new one with the desired condition as follows:
- Drop the existing constraint:
1
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
- Add the new
CHECKSQL constraint:
1
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (new_condition);
How to use SQL CHECK for duplicates?
Using the SQL CHECK for duplicates is uncommon. Instead, you should create a UNIQUE constraint on the columns where duplicates should not occur. Alternatively, you can make use of the sort statement on Unix architectures with the -u (unique) flag to quickly delete duplicates from any file.
How to use SQL CHECK for NULL values?
You use CHECK constraints to check for IS NOT NULL values in a column. At the same time, this is usually done by marking that column as not nullable at definition time.
What role does CHECK play in PostgreSQL data integrity?
In PostgreSQL, CHECK constraints play a crucial role in ensuring data integrity by enforcing specific conditions on column values. These constraints enable you to validate data entered into a table, ensuring it meets predefined criteria. For more guidance, follow our article on PostgreSQL data integrity.
What happens in case of a CHECK constraint violation?
In case of a CHECK constraint violation in SQL, the database management system rejects the operation with an error. For instance, if an INSERT or UPDATE statement attempts to add or modify a row with data that does not meet the condition of the CHECK constraint, the operation fails. This ensures data integrity by preventing invalid or inconsistent data from being inserted or updated in the database.
