Db2 CHECK Constraint

Summary: in this tutorial, you will learn how to use the Db2 CHECK constraint to enforce data integrity in a table.

Db2 CHECK constraint overview

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.

To define a check constraint for a column, you use the following syntax:

CREATE TABLE table_name(
	...
	column_name type CHECK(Boolean_expression),
	...
);
Code language: SQL (Structured Query Language) (sql)

When you insert or update value into the column_name, Db2 evaluates the Boolean_expression of the check constraint. If the Boolean_expression returns false, Db2 will reject the operation; otherwise, Db2 will allow.

Sometimes, you may want to check values across columns of a table. To do this, you use the following syntax:

CREATE TABLE table_name(
	column_name1 type,
	column_name2 type,
	...,
	CHECK(Boolean_expression)
);
Code language: SQL (Structured Query Language) (sql)

The Boolean_expression in this syntax may include multiple columns of the table.

Note that the Boolean expression specified in the CHECK constraint only can include the columns within the same table. It cannot have columns from other tables.

To add a check constraint to a table, you use the following syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK(Boolean_expression);
Code language: SQL (Structured Query Language) (sql)

To remove a check constraint from a table, you use this syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Code language: SQL (Structured Query Language) (sql)

Db2 CHECK constraint examples

Let’s take some examples of using the Db2 CHECK constraint.

1) Creating a CHECK constraint when creating the table example

First, create the book_prices table that stores the book prices:

CREATE TABLE book_prices(
	book_id INT NOT NULL,
	valid_from DATE NOT NULL,
	price DEC(10,2) CHECK(price > 0),
	PRIMARY KEY(book_ID, valid_from),
	FOREIGN KEY(book_id) 
		REFERENCES books(book_id)
			ON UPDATE RESTRICT
			ON DELETE CASCADE
);
Code language: SQL (Structured Query Language) (sql)

In this example, the price column has a check constraint that allows only positive price to be inserted or updated to the column.

Second, insert a new row into the books_price table whose price is positive.

INSERT INTO 
	book_prices(book_id, valid_from, price)
VALUES
	(1, '2019-05-06',29.99);
Code language: SQL (Structured Query Language) (sql)

The statement works because the price is positive.

Third, attempt to insert a zero price into the book_prices table:

INSERT INTO 
	book_prices(book_id, valid_from, price)
VALUES
	(2, '2019-05-06',0);
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following error because the price does not pass the check:

SQL0545N  The requested operation is not allowed because a row does not satisfy
the check constraint "DB2ADMIN.BOOK_PRICES.SQL190605115402090".Code language: CSS (css)

Note that SQL190605115402090 is the constraint name generated automatically by Db2.

2) Adding a CHECK constraint to an existing table example

First, add a new column named to the book_prices table using the ALTER TABLE ADD COLUMN statement:

ALTER TABLE book_prices
ADD COLUMN cost DEC(10,2);
Code language: SQL (Structured Query Language) (sql)

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:

ALTER TABLE book_prices
ADD CONSTRAINT cost_check 
CHECK(cost > 0 AND price >= cost);
Code language: SQL (Structured Query Language) (sql)

Third, insert a row into the book_prices table:

INSERT INTO 
	book_prices(book_id, valid_from, cost, price)
VALUES
	(3, '2019-05-15',15.99, 20.99);
Code language: SQL (Structured Query Language) (sql)

The statement works because both cost and price pass the condition in the check constraint.

Fourth, insert a row whose cost is greater than the price:

INSERT INTO 
	book_prices(book_id, valid_from, cost, price)
VALUES
	(4, '2019-05-15',16.99, 12.99);
Code language: SQL (Structured Query Language) (sql)

Db2 issued the following error:

SQL0545N  The requested operation is not allowed because a row does not satisfy
the check constraint "DB2ADMIN.BOOK_PRICES.COST_CHECK".
Code language: SQL (Structured Query Language) (sql)

Fifth, insert a row whose cost is NULL:

INSERT INTO 
	book_prices(book_id, valid_from, cost, price)
VALUES
	(5, '2019-05-15',NULL, 13.99);
Code language: SQL (Structured Query Language) (sql)

It worked because the condition cost > 0 AND price >= cost evaluates to unknown when the cost is NULL.

To avoid this kind of bypassing the check constraint, you need to use the NOT NULL constraint for the cost column.

3) Removing a CHECK constraint example

The following statement removes the cost_check check constraint from the the book_prices table:

ALTER TABLE book_prices
DROP CONSTRAINT cost_check;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 CHECK constraint to enforce data integrity in a table.