Check Constraints

A check constraint can be defined as follows:

CHECK (< predicate >)

The predicate accepts the values TRUE, FALSE, or UNKNOWN. If it takes the FALSE value, the constraint is considered to be violated, the action that caused this violation is cancelled, and an error message is generated.

Let’s get back to our problem – inserting the row (NULL,    ‘2000’,    NULL) into the Product table. It’s obvious such data should be avoided, since it’s not clear to what type of product the model 2000 belongs and who produces it. We can use the IS NOT NULL predicate in the check constraints for the columns maker and type:

DROP TABLE Product;
create table Product (
maker varchar(10),
model varchar(50),
type varchar(50),
CONSTRAINT product_PK PRIMARY KEY (model),
CONSTRAINT maker_ch CHECK(maker IS NOT NULL),
CONSTRAINT type_ch CHECK(type IS NOT NULL)
);

Now, if we attempt to add the row (NULL,    ‘2000’,    NULL) we’ll get the following error message:

The row won’t be inserted, or, to be more precise, the whole INSERT statement will be rejected.

Note that the NOT NULL constraint can be included into the column definition itself (just as the PRIMARY KEY constraint for a simple key):

DROP TABLE Product;
create table Product (
maker varchar(10) NOT NULL,
model varchar(50) PRIMARY KEY,
type varchar(50) NOT NULL
);

Moreover, any column-level constraint can be written down directly in definition of a column. Below is the example adding a new column (named available) to Product table:

ALTER TABLE Product ADD available VARCHAR(20) DEFAULT 'Yes';

To restrict admissible values, which can appear in the column, by the values ’ Yes’ and ’ No ‘, this statement could be rewritten in the form of:

ALTER TABLE Product ADD available VARCHAR(20)
CHECK(available in('Yes','No')) DEFAULT 'Yes';