A check constraint can be defined as follows:
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:
Now, if we attempt to add the row (NULL, '2000', NULL) we’ll get the following error message:
Cannot insert the value NULL into column 'model', table 'Product'; column does not allow nulls. INSERT fails.
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):
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:
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: