10.3 The ForeignKeyConstraint

The ForeignKeyConstraint provides an easy way to impose referential integrity rules on records in a DataSet. The ForeignKeyConstraint serves two purposes: it prevents you from making DataSet changes that violate referential integrity, and it allows you to define what action to take with child rows when parent rows are updated or deleted.

When creating a ForeignKeyConstraint, you specify the parent and child DataColumn and optionally name the constraint. You add the ForeignKeyConstraint to the child DataTable . For example, the following code creates and applies a ForeignKeyConstraint that relates product records to a specific category:

ForeignKeyConstraint fc = new ForeignKeyConstraint("CategoryID",
  ds.Tables["Categories"].Columns["CategoryID"], 
  ds.Tables["Products"].Columns["CategoryID"]);

ds.Tables["Products"].Constraints.Add(fc);

Before performing this step, you should add a UniqueKeyConstraint on the CategoryID column in the parent (Categories) table to ensure that every relation can be resolved.

You can also pass constructor arguments to several overloads of the Add( ) method of a ConstraintCollection to create a ForeignKeyConstraint. The following code is equivalent to the previous example:

// Add a new ForeignKeyConstraint to the table's Constraints collection.
dt.Constraints.Add("CategoryID",
  ds.Tables["Categories"].Columns["CategoryID"], 
  ds.Tables["Products"].Columns["CategoryID"]);

When a ForeignKeyConstraint is in place, and constraint checking is enabled for the DataSet, all child column values (except nulls, if they are allowed) must point to an existing parent row.

10.3.1 Referential Integrity with ForeignKeyConstraint Rules

The ForeignKeyConstraint doesn't just restrict changes; it can also propagate them depending on the value of three key properties: DeleteRule, UpdateRule, and AcceptRejectRule:

• The DeleteRule determines what happens to child rows when a parent is deleted. By default, this is Cascade, which means all child rows are deleted along with the parent.

• The UpdateRule determines what happens if the parent's key column is modified. The default is Cascade, which means that the child rows are updated to point to the new value.

• The AcceptRejectRule determines what happens when the DataRow.AcceptChanges( ) method is called (usually as part of a data source update). By default, this is None.

Generally, DeleteRule is the most important of these settings. The UpdateRule isn't frequently used because you rarely change the value in a primary key column. In fact, this field often corresponds to an identity value that is generated by the data source. Tables 10-1 and 10-2 present the options for the Rule enumeration when used to set the DeleteRule and UpdateRule properties,

Table 10-1. Values for the DeleteRule

Value	Used for DeleteRule
Cascade	If the parent row is deleted, the child rows are also deleted.
None	No action is taken on child rows. Thus, if you try to delete a parent that has linked children, an exception will be thrown. This is the default SQL Server behavior.
SetDefault	If the parent row is deleted, the child rows have the default value placed in their foreign key column, if allowed (otherwise an exception is thrown).
SetNull	If the parent row is deleted, the foreign key column of all children is set to null. If the DataColumn.AllowDbNull property disallows this, an exception is thrown.

Table 10-2. Values for the UpdateRule

Value	Used for UpdateRule
Cascade	If the linked column is changed in the parent, the foreign key column in all child rows is updated accordingly.
None	No action is taken on child rows. Thus, if you change the linked column of a parent that has children, an exception is thrown. This is the traditional SQL Server behavior.
SetDefault	If the linked column of a parent row is changed, the foreign key column in the child rows is reset to the default value, if allowed (otherwise an exception is thrown).
SetNull	If the linked column of the parent row is changed, the foreign key column of all children is set to null. If the DataColumn.AllowDbNull property disallows this, an exception is thrown.

The AcceptRejectRule, on the other hand, can accept a value of Cascade or None from the AcceptRejectRule enumeration. If you set this value to Cascade, the AcceptChanges( ) method is called on child rows when the parent row is updated. This is rarely the behavior you want. In fact, because the AcceptChanges( ) method resets the DataRow.RowState to Unchanged, this technique may actually prevent child rows from being updated when you merge changes back into the data source! Thus, it's strongly recommended that you use the default of None, unless you aren't intending to commit DataSet changes.

Like UniqueConstraints, the usefulness of ForeignKeyConstraints may be limited by the fact that the DataSet contains only a subset of information from the DataSet. Thus, if you use a ForeignKeyConstraint, you can't insert child rows that don't have corresponding parent rows in the DataSet, even if these parent rows legitimately exist in the data source. However, you can still create and use DataRelation objects, as described in Chapter 11.