ALTER TABLE statement
The following levels of check constraints can be distinguished:
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:
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:
Now, we add our constraint to the table and check how it works.
To make sure the constraint functions as it should, let’s try adding a model of a new type:
As expected, we get the following error message in response:
The INSERT statement conflicted with the CHECK constraint "chk_type". The conflict occurred in database "learn", table "dbo.product", column 'type'. The statement has been terminated.
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.
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
And of course, other makers may produce any kind of product: