PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 66

Changing the values of primary keys isn't something we recommend
you do often, but in some situations you may find yourself needing to do
just that. If you find yourself in that situation often, you might consider set-
ting up an update rule on your foreign keys.
Constraints
SQL Server contains several types of constraints to enforce data integrity.
Constraints, as the name implies, are used to constrain the values that can
be entered into columns. We have talked about two of the constraints in
SQL Server: primary keys and foreign keys. Primary keys constrain the
data so that duplicates and NULLs cannot exist in the columns, and for-
eign keys ensure that the entered value exists in the referenced table.
There are several other constraints you can implement to ensure data in-
tegrity or enforce business rules.
Unique Constraints
Unique constraints are similar to primary keys; they ensure that no du-
plicates exist in a column or collection of columns. They are configured on
columns that do not participate in the primary key. How does a unique con-
straint differ from a primary key? From a technical standpoint, the only dif-
ference is that a unique constraint allows you to enter NULL values;
however, because the values must be unique, you can enter only one NULL
value for the entire column. When we talked about identifying primary
keys, we talked about candidate keys. Because candidate keys should also
be able to uniquely identify the row, you should probably place unique con-
straints on your candidate keys. You add a unique constraint in much the
same way as you add a foreign key, using a constraint statement such as
CONSTRAINT UNQ_vehicle_vin UNIQUE NONCLUSTERED (vin_number)
Check Constraints
Check constraints limit the values that can be entered into a column by
using a logical expression. A logical expression is any SQL expression
that can evaluate to TRUE or FALSE. The expression can be any valid
SQL expression, from simple comparisons to something more complex
such as calling a function. For example, say we want to limit the values that
can be entered for salary in our employee table. The expression we would
use to evaluate the data would be something like this: