Data Definition Language (SQL DDL)

The following levels of check constraints can be distinguished:

•  attribute (column) level,
•  tuple (row) level,
•  relation (table) level.

An attribute-level constraint is applied to a single column, in other words, this type of constraint refers to just one column of the table whose definition contains this constraint. For an example, let’s get back to the Computer firm database. The type column of its Product table can contain one of three values. We can forbid entering any other information into this column by using the following constraint:

CHECK (type IN('printer', 'pc', 'laptop'))

 Let’s make a digression to familiarize ourselves with the ALTER TABLE statement that allows modifying the table structure without re-creating it anew each time. This is particularly important since a structure change may become necessary at a moment when the table already contains data.

The ALTER TABLE statement lets you add and delete columns, default values, and constraints.

At this point, we’re interested in applying a constraint to the type column; thus, let’s examine the statement syntax for adding constraints first:

ALTER TABLE < TABLE name >
ADD CONSTRAINT < constraint name > < constraint >;

Now, we add our constraint to the table and check how it works.

ALTER TABLE Product
ADD CONSTRAINT chk_type CHECK (type IN('pc', 'laptop', 'printer'));

To make sure the constraint functions as it should, let’s try adding a model of a new type:

INSERT INTO Product VALUES('A', 1122, 'notebook');

As expected, we get the following error message in response:

As one could easily have guessed, a row-level constraint refers to several columns, and is checked individually for each record to be changed. The row can be added (or changed) if its data doesn’t violate the constraint. 

For example, let’s forbid the maker Z producing anything but printers.

ALTER TABLE Product
ADD constraint chk_maker_Z CHECK ((maker='Z' AND type= 'printer') OR maker <>'Z');

Thus, the constraint ensures the model in the Product table is a printer by the maker Z (maker='Z' and type= 'printer') or is produced by any other maker (but not Z).

If we try to add a PC model by maker Z

INSERT INTO Product VALUES('Z', 1122, 'PC');

INSERT INTO Product VALUES('Z', 1122, 'Printer');

And of course, other makers may produce any kind of product:

INSERT INTO Product VALUES('V', 1123, 'PC');