{"id":566,"date":"2016-04-15T07:26:58","date_gmt":"2016-04-15T07:26:58","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=566"},"modified":"2025-02-08T01:15:31","modified_gmt":"2025-02-08T08:15:31","slug":"sql-check-constraint","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-check-constraint\/","title":{"rendered":"SQL CHECK Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL CHECK<\/code> constraint to ensure data in columns meet specific conditions.<\/p>\n\n\n\n

Introduction to SQL CHECK constraint #<\/a><\/h2>\n\n\n\n

In SQL, a check constraint ensures data in one or more columns within a table meet a specific condition. The CHECK<\/code> constraints help you maintain data accuracy by limiting the value columns can store.<\/p>\n\n\n\n

Here’s the syntax for defining a CHECK<\/code> constraint for a column:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name (\n    column1 data_type CHECK<\/span>(boolean_expression)\n    ...\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you define a CHECK<\/code> constraint for a column, the CHECK<\/code> constraint can check the values for that column only. In other words, the boolean_expression<\/code> can only access the values of the column where you define the CHECK<\/code> constraint.<\/p>\n\n\n\n
To define a CHECK<\/code> constraint for multiple columns, you define it as a table constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name (\n    column1 datatype constraint<\/span>,\n    column2 datatype constraint<\/span>,\n    CHECK<\/span> (boolean_expression)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the boolean_expression<\/code> can access the values of all the columns of the table.<\/p>\n\n\n\n
Defining a CHECK constraint as a column constraint #<\/a><\/h2>\n\n\n\n
First, create a table<\/a> called offers<\/code> to store job offers:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> offers (\n  offer_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  offer_date DATE<\/span> NOT<\/span> NULL<\/span>,\n  job_id INT<\/span> NOT<\/span> NULL<\/span>,\n  candidate VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  salary DECIMAL<\/span>(19<\/span>, 2<\/span>) NOT<\/span> NULL<\/span> CHECK<\/span> (salary > 0<\/span>),\n  FOREIGN<\/span> KEY<\/span> (job_id) REFERENCES<\/span> jobs (job_id) ON<\/span> DELETE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this table, we define a CHECK<\/code> constraint for the salary to ensure that it is always greater than zero:<\/p>\n\n\n
salary DECIMAL(19, 2) NOT NULL CHECK<\/span> (salary > 0<\/span>)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, attempt to insert a row into the salary<\/code> column of the offers<\/code> table with the value of zero:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  offers (offer_date, job_id, candidate, salary)\nVALUES<\/span>\n  ('1994-05-07'<\/span>, 1<\/span>, 'William Giet'<\/span>, 0<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The database system will issue an error like this:<\/p>\n\n\n
ERROR:  new row for relation \"offers\" violates check<\/span> constraint<\/span> \"offers_salary_check\"<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the new row does not meet the constraint offers_salary_check<\/code>.<\/p>\n\n\n\n
Note that the offers_salary_check<\/code> is a generated constraint name by the database system. It may vary depending on the database system.<\/p>\n\n\n\n
Third, insert a row with a valid salary into the offers<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  offers (offer_date, job_id, candidate, salary)\nVALUES<\/span>\n  ('1994-05-07'<\/span>, 1<\/span>, 'William Giet'<\/span>, 8300<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, retrieve data<\/a> from the offers<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  offer_date,\n  job_id,\n  candidate,\n  salary\nFROM<\/span>\n  offers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 offer_date | job_id |  candidate   | salary\n------------+--------+--------------+---------<\/span>\n 1994-05-07 |      1 | William Giet | 8300.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Defining a CHECK constraint as a table constraint #<\/a><\/h2>\n\n\n\n
First, create a table called job_postings<\/code> to store the opening job postings:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> job_postings (\n  id<\/span> INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  job_title VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n  description TEXT<\/span>,\n  min_salary DEC<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n  max_salary DEC<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n  posting_date DATE<\/span>,\n  closing_date DATE<\/span>,\n  CHECK<\/span> (\n    min_salary > 0<\/span>\n    AND<\/span> min_salary <= max_salary\n  ),\n  CHECK<\/span> (closing_date >= posting_date)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we define two CHECK<\/code> constraints as table constraints, which include multiple multiple columns.<\/p>\n\n\n\n
The following CHECK<\/code> constraint ensures that the minimum salary is greater than zero and less than or equal to the maximum salary:<\/p>\n\n\n
CHECK<\/span> (\n  min_salary > 0<\/span>\n  AND<\/span> min_salary <= max_salary\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following CHECK<\/code> constraint ensures that the posting date is earlier than the closing date:<\/p>\n\n\n
CHECK<\/span> (closing_date >= posting_date)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Assigning CHECK constraint names #<\/a><\/h2>\n\n\n\n
SQL allows you to assign an explicit name to a CHECK<\/code> constraint via the CONSTRAINT<\/code> clause:<\/p>\n\n\n
CONSTRAINT constraint_name \nCHECK<\/span>(boolean_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The constraint name will help you clarify the error message returned by the database system when the constraint violation occurs. <\/p>\n\n\n\n
For example:<\/p>\n\n\n\n
First, create a table called hires<\/code> with a CHECK<\/code> constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> hires (\n  hire_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  offer_id INT<\/span> NOT<\/span> NULL<\/span>,\n  hire_date DATE<\/span> NOT<\/span> NULL<\/span>,\n  start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n  CONSTRAINT<\/span> check_start_date CHECK<\/span> (start_date >= hire_date)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we define a CHECK<\/code> constraint called check_start_date<\/code> to ensure that the start date must be on or after the hire date.<\/p>\n\n\n\n
Second, insert a new row into the hires table with the start date before the hire date:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  hires (offer_id, hire_date, start_date)\nVALUES<\/span>\n  (1<\/span>, '1994-06-07'<\/span>, '1994-06-01'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The database system will issue the following error:<\/p>\n\n\n
ERROR:  new row for relation \"hires\" violates check<\/span> constraint<\/span> \"check_start_date\"<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The error message includes the CHECK<\/code> constraint name check_start_date<\/code> so that you can use to troubleshoot.<\/p>\n\n\n\n
Note that your database system may issue a slightly different error message.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
In this syntax, you specify the CHECK<\/code> constraint in the column definition when creating the table. When you insert<\/a> or update<\/a> values, they must pass the boolean_expression<\/code>.<\/p>\n\n\n\n