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 :
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
Table 10-2. Values for the UpdateRule
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.