Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 212

ON UPDATE SET DEFAULT: All the values in the foreign key column are reset to default
value. In the OrderDetail example, when a row is updated in the Order table then all the rows in
OrderDetail where the OrderID equals the OrderID from the updated row would get the default
value defined for the column, in this case -1.

❑

Unique Constraints
A unique constraint on a column ensures that no two values can be the same for all the rows in a
table. Unique constraints and primary keys are very similar; however, there are a few differences to be
aware of:

❑ First, a table can only have one primary key. It can have many unique constraints, though.

❑ Second, a unique constraint can allow for a NULL value. A primary key cannot. Note that the

table can only have one row where the column value is NULL.

Unique constraints are sometimes known as alternate keys. Because of their unique nature they can also
participate in a foreign key constraint.

Check Constraints
Check constraints enforce integrity by limiting values that are allowed in a column. These are similar to
foreign keys. The difference is that foreign keys get their list of valid values from another table. Check
constraints get their list of valid values from the column definition.

Check constraints are expressions that must evaluate to true or false. They can be defined on a single
column of a table or multiple columns. A constraint that operates on multiple columns is known as a
table-level constraint.

A column-level constraint might restrict entries to certain values. Suppose you have a table with a Gender
column in it. You only want to allow two values: M or F. A constraint can be created: Gender = M OR
Gender = F.

For a table-level constraint, suppose you have the table shown in Figure 8-17.

Figure 8-17

You might create a constraint such that (DepartureDate > ArrivalDate).

Nullability
Columns can either allow nulls or not. A null is not the same thing as zero or blank. Null indicates that
a value is unknown or doesn't exist. It's generally recommended that columns shouldn't allow nulls.