{"id":599,"date":"2019-06-04T22:27:42","date_gmt":"2019-06-05T06:27:42","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=599"},"modified":"2020-06-29T23:16:06","modified_gmt":"2020-06-30T07:16:06","slug":"db2-check-constraint","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-check-constraint\/","title":{"rendered":"Db2 CHECK Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 CHECK<\/code> constraint to enforce data integrity in a table.<\/p>\n\n\n\n

Db2 CHECK constraint overview<\/h2>\n\n\n\n

A check constraint is a rule that specifies what values are allowed to be stored in a table. For example, you can specify that the price of a book must be positive.<\/p>\n\n\n\n

To define a check constraint for a column, you use the following syntax:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name(\n\t...\n\tcolumn_name type<\/span> CHECK<\/span>(Boolean_expression),\n\t...\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you insert or update value into the column_name<\/code>, Db2 evaluates the Boolean_expression<\/code> of the check constraint. If the Boolean_expression<\/code> returns false, Db2 will reject the operation; otherwise, Db2 will allow.<\/p>\n\n\n\n
Sometimes, you may want to check values across columns of a table. To do this, you use the following syntax:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name(\n\tcolumn_name1 type<\/span>,\n\tcolumn_name2 type<\/span>,\n\t...,\n\tCHECK<\/span>(Boolean_expression)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The Boolean_expression<\/code> in this syntax may include multiple columns of the table.<\/p>\n\n\n\n
Note that the Boolean expression specified in the CHECK<\/code> constraint only can include the columns within the same table. It cannot have columns from other tables.<\/p>\n\n\n\n
To add a check constraint to a table, you use the following syntax:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nADD<\/span> CONSTRAINT<\/span> constraint_name\nCHECK<\/span>(Boolean_expression);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To remove a check constraint from a table, you use this syntax:<\/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
Db2 CHECK constraint examples<\/h2>\n\n\n\n
Let’s take some examples of using the Db2 CHECK<\/code> constraint.<\/p>\n\n\n\n
1) Creating a CHECK constraint when creating the table example<\/h3>\n\n\n\n
First, create<\/a> the book_prices<\/code> table that stores the book prices:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> book_prices(\n\tbook_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tvalid_from DATE<\/span> NOT<\/span> NULL<\/span>,\n\tprice DEC<\/span>(10<\/span>,2<\/span>) CHECK<\/span>(price > 0<\/span>),\n\tPRIMARY KEY<\/span>(book_ID, valid_from),\n\tFOREIGN<\/span> KEY<\/span>(book_id) \n\t\tREFERENCES<\/span> books(book_id)\n\t\t\tON<\/span> UPDATE<\/span> RESTRICT\n\t\t\tON<\/span> DELETE<\/span> CASCADE<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the price<\/code> column has a check constraint that allows only positive price to be inserted or updated to the column.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> \n\tbook_prices(book_id, valid_from, price)\nVALUES<\/span>\n\t(1<\/span>, '2019-05-06'<\/span>,29.99<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement works because the price is positive.<\/p>\n\n\n\n
Third, attempt to insert a zero price into the book_prices<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n\tbook_prices(book_id, valid_from, price)\nVALUES<\/span>\n\t(2<\/span>, '2019-05-06'<\/span>,0<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 issued the following error because the price does not pass the check:<\/p>\n\n\n
SQL0545N<\/span>  The<\/span> requested<\/span> operation<\/span> is<\/span> not<\/span> allowed<\/span> because<\/span> a<\/span> row<\/span> does<\/span> not<\/span> satisfy<\/span>\nthe<\/span> check<\/span> constraint<\/span> \"DB2ADMIN<\/span>.BOOK_PRICES<\/span>.SQL190605115402090<\/span>\".<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that SQL190605115402090<\/code> is the constraint name generated automatically by Db2.<\/p>\n\n\n\n
2) Adding a CHECK constraint to an existing table example<\/h3>\n\n\n\n
First, add a new column<\/a> named to the book_prices<\/code> table using the ALTER TABLE ADD COLUMN<\/a><\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> book_prices\nADD<\/span> COLUMN<\/span> cost<\/span> DEC<\/span>(10<\/span>,2<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, add a check constraint to make sure that the cost is greater than zero and the price is always greater than or equal to the cost:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> book_prices\nADD<\/span> CONSTRAINT<\/span> cost_check \nCHECK<\/span>(cost<\/span> > 0<\/span> AND<\/span> price >= cost<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert a row into the book_prices<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n\tbook_prices(book_id, valid_from, cost<\/span>, price)\nVALUES<\/span>\n\t(3<\/span>, '2019-05-15'<\/span>,15.99<\/span>, 20.99<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement works because both cost and price pass the condition in the check constraint.<\/p>\n\n\n\n
Fourth, insert a row whose cost is greater than the price:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n\tbook_prices(book_id, valid_from, cost<\/span>, price)\nVALUES<\/span>\n\t(4<\/span>, '2019-05-15'<\/span>,16.99<\/span>, 12.99<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 issued the following error:<\/p>\n\n\n
SQL0545N  The requested operation is not allowed because a row does not satisfy\nthe check<\/span> constraint<\/span> \"DB2ADMIN.BOOK_PRICES.COST_CHECK\"<\/span>.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, insert a row whose cost is NULL:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n\tbook_prices(book_id, valid_from, cost<\/span>, price)\nVALUES<\/span>\n\t(5<\/span>, '2019-05-15'<\/span>,NULL<\/span>, 13.99<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It worked because the condition cost > 0 AND price >= cost<\/code> evaluates to unknown<\/code> when the cost is NULL.<\/p>\n\n\n\n
To avoid this kind of bypassing the check constraint, you need to use the NOT NULL<\/code> constraint for the cost<\/code> column.<\/p>\n\n\n\n
3) Removing a CHECK constraint example<\/h3>\n\n\n\n
The following statement removes the cost_check check constraint from the the book_prices<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> book_prices\nDROP<\/span> CONSTRAINT<\/span> cost_check;\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 Db2 CHECK<\/code> constraint to enforce data integrity 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
Second, insert a new row<\/a> into the books_price<\/code> table whose price is positive.<\/p>\n\n\n