{"id":595,"date":"2018-06-03T15:58:30","date_gmt":"2018-06-03T08:58:30","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=595"},"modified":"2020-04-11T20:13:24","modified_gmt":"2020-04-11T13:13:24","slug":"sql-server-check-constraint","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-check-constraint\/","title":{"rendered":"SQL Server CHECK Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server CHECK<\/code> constraint to enforce domain integrity.<\/p>\n\n\n\n

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

The CHECK<\/code> constraint allows you to specify the values in a column that must satisfy a Boolean expression.<\/p>\n\n\n\n

For example, to require positive unit prices, you can use:<\/p>\n\n\n

CREATE<\/span> SCHEMA<\/span> test<\/span>;\nGO\n\nCREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>) CHECK<\/span>(unit_price > 0<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As you can see, the CHECK<\/code> constraint definition comes after the data type. It consists of the keyword CHECK<\/code> followed by a logical expression in parentheses:<\/p>\n\n\n
CHECK<\/span>(unit_price > 0<\/span>)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can also assign the constraint a separate name by using the CONSTRAINT<\/code> keyword as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>) CONSTRAINT<\/span> positive_price CHECK<\/span>(unit_price > 0<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The explicit names help classify the error messages and allow you to refer to the constraints when you want to modify them.<\/p>\n\n\n\n
If you don’t specify a constraint name this way, SQL Server automatically generates a name for you.<\/p>\n\n\n\n
See the following insert statement:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.products(product_name, unit_price)\nVALUES<\/span> ('Awesome Free Bike'<\/span>, 0<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued the following error:<\/p>\n\n\n
The INSERT<\/span> statement<\/span> conflicted with<\/span> the CHECK<\/span> constraint<\/span> \"positive_price\"<\/span>. The conflict occurred in<\/span> database<\/span> \"BikeStores\"<\/span>, table<\/span> \"test.products\"<\/span>, column<\/span> 'unit_price'<\/span>.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The error occurred because the unit price is not greater than zero as specified in the CHECK<\/code> constraint.<\/p>\n\n\n\n
The following statement works fine because the logical expression defined in the CHECK<\/code> constraint evaluates to TRUE<\/code>:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.products(product_name, unit_price)\nVALUES<\/span> ('Awesome Bike'<\/span>, 599<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server CHECK constraint and NULL #<\/a><\/h2>\n\n\n\n
The CHECK<\/code> constraints reject values that cause the Boolean expression evaluates to FALSE<\/code>.<\/p>\n\n\n\n
Because NULL<\/code> evaluates to UNKNOWN<\/code>, it can be used in the expression to bypass a constraint.<\/p>\n\n\n\n
For example, you can insert<\/a> a product whose unit price is NULL<\/code> as shown in the following query:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.products(product_name, unit_price)\nVALUES<\/span> ('Another Awesome Bike'<\/span>, NULL<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server inserted NULL<\/code> into the unit_price<\/code> column and did not return an error.<\/p>\n\n\n\n
To fix this, you need to use a NOT NULL<\/code> constraint for the unit_price<\/code> column.<\/p>\n\n\n\n
CHECK constraint referring to multiple columns #<\/a><\/h2>\n\n\n\n
A CHECK<\/code> constraint can refer to multiple columns. For instance, you store a regular and discounted prices in the  test.products<\/code> table and you want to ensure that the discounted price is always lower than the regular price:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>) CHECK<\/span>(unit_price > 0<\/span>),\n    discounted_price DEC<\/span>(10<\/span>,2<\/span>) CHECK<\/span>(discounted_price > 0<\/span>),\n    CHECK<\/span>(discounted_price < unit_price)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The first two constraints for unit_price<\/code> and discounted_price<\/code> should look familiar.<\/p>\n\n\n\n
The third constraint uses a new syntax which is not attached to a particular column. Instead, it appears as a separate line item in the comma-separated column list.<\/p>\n\n\n\n
The first two column constraints are column constraints, whereas the third one is a table constraint.<\/p>\n\n\n\n
Note that you can write column constraints as table constraints. However, you cannot write table constraints as column constraints. For example, you can rewrite the above statement as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>),\n    discounted_price DEC<\/span>(10<\/span>,2<\/span>),\n    CHECK<\/span>(unit_price > 0<\/span>),\n    CHECK<\/span>(discounted_price > 0<\/span>),\n    CHECK<\/span>(discounted_price > unit_price)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
or even:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>),\n    discounted_price DEC<\/span>(10<\/span>,2<\/span>),\n    CHECK<\/span>(unit_price > 0<\/span>),\n    CHECK<\/span>(discounted_price > 0<\/span> AND<\/span> discounted_price > unit_price)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can also assign a name to a table constraint in the same way as a column constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>),\n    discounted_price DEC<\/span>(10<\/span>,2<\/span>),\n    CHECK<\/span>(unit_price > 0<\/span>),\n    CHECK<\/span>(discounted_price > 0<\/span>),\n    CONSTRAINT<\/span> valid_prices CHECK<\/span>(discounted_price > unit_price)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Add CHECK constraints to an existing table #<\/a><\/h2>\n\n\n\n
To add a CHECK<\/code> constraint to an existing table, you use the ALTER TABLE ADD CONSTRAINT<\/code> statement.<\/p>\n\n\n\n
Suppose you have the following test.products<\/code> table:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.products(\n    product_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    unit_price DEC<\/span>(10<\/span>,2<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To add a CHECK<\/code> constraint to the test.products<\/code> table, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> test.products\nADD<\/span> CONSTRAINT<\/span> positive_price CHECK<\/span>(unit_price > 0<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To add a new column with a CHECK<\/code> constraint, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> test.products\nADD<\/span> discounted_price DEC<\/span>(10<\/span>,2<\/span>)\nCHECK<\/span>(discounted_price > 0<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To add a CHECK<\/code> constraint named valid_price<\/code>, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> test.products\nADD<\/span> CONSTRAINT<\/span> valid_price \nCHECK<\/span>(unit_price > discounted_price);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Remove CHECK constraints #<\/a><\/h2>\n\n\n\n
To remove a CHECK<\/code> constraint, you use the ALTER TABLE DROP CONSTRAINT<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nDROP<\/span> CONSTRAINT<\/span> constraint_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you assign a CHECK<\/code> constraint a specific name, you can refer the name in the statement.<\/p>\n\n\n\n
However, in case you did not assign the CHECK<\/code> constraint a particular name, then you need to find it using the following statement:<\/p>\n\n\n
EXEC sp_help 'table_name';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
EXEC sp_help 'test.products';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement issues a lot of information including constraint names:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following statement drops the positive_price<\/code> constraint:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> test.products\nDROP<\/span> CONSTRAINT<\/span> positive_price;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Disable CHECK constraints for insert or update #<\/a><\/h2>\n\n\n\n
To disable a CHECK<\/code> constraint for insert or update, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nNOCHECK<\/span> CONSTRAINT<\/span> constraint_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement disables the valid_price<\/code> constraint:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> test.products\nNO<\/span> CHECK<\/span> CONSTRAINT<\/span> valid_price;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL Server CHECK<\/code> constraint to limit the values that can be inserted or updated to one or more columns in a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t