Constraints

for RuBoard

As you're probably aware by now, you can place constraints on tables to ensure that the data in them conforms to certain rules. Among these are unique constraints, primary keys, and foreign keys.

Applying Unique Constraints

As shown in the previous section, you can use the Unique property to enforce uniqueness on a DataColumn by setting the property to True . Conversely, you can remove the constraint simply by setting the Unique property to False . However, unique constraints can also include more than one column so that the combination of columns will be unique. Because setting each of the column's Unique properties to True won't enforce the rule that their combination must be unique, you need some way to group the unique columns together. This can be done with a UniqueConstraint object. For example, the following code snippet creates a unique constraint on the OrderID and ISBN columns of the orderDet DataTable , which holds OrderDetails records:

 Dim unique As New UniqueConstraint("OrderID_ISBN", _   New DataColumn() {orderDet.Columns("OrderID"), orderDet.Columns("ISBN")} ) orderDet.Constraints.Add(unique) 

Once the constraint has been created, it must be added to the Constraints collection of the DataTable using the overloaded Add method. The Constraints property exposes a ConstraintCollection object that can hold any constraint objects derived from the System.Data.Constraint class (as is UniqueConstraint ). The previous code snippet could also have been rewritten in the following way:

 orderDet.Constraints.Add("OrderID_ISBN", _   New DataColumn() {orderDet.Columns("OrderID"), _   orderDet.Columns("ISBN")} , False) 

The last argument to the Add method in this case specifies that the unique constraint shouldn't also be considered a primary key.

Specifying Primary Keys

As the previous code snippet makes clear, primary keys are simply special instances of unique constraints in the ConstraintCollection of a DataTable . In fact, the UniqueConstraint class exposes a read-only IsPrimaryKey property that returns True when the constraint is acting as the primary key. As with unique constraints, primary keys can span one or more columns. Of course, one of the differences with primary keys is that they can also be set automatically when the DataSet is populated from a data adapter when the MissingSchemaAction property is set to AddWithKey .

Primary keys can be programmatically created in two ways: either by using the PrimaryKey property of the DataTable or by creating a unique constraint through the Add method of the ConstraintCollection as shown earlier. For example, the former method is shown in the following code snippet:

 orderDet.PrimaryKey = New DataColumn() {orderDet.Columns("OrderID"), _   orderDet.Columns("ISBN")} 

In this case, the primary key is set to a composite key of the OrderID and ISBN columns. Simply setting the PrimaryKey property also adds the constraint to the ConstraintCollection .

Using Foreign Key Constraints and Relations

The final type of constraint that you can place in a DataSet is the foreign key constraint represented by the ForeignKeyConstraint class derived from Constraint . As in a relational database, a foreign key constraint is simply a pointer to the primary key of another table and, as a result, lives in the ConstraintCollection of the child table. Also as in a relational database, a foreign key constraint has two primary functions. First, it makes sure that if a child row is added to the DataTable , there is a corresponding row in the parent table. Second, it controls whether changes to the primary key of the parent table are cascaded to the child table or disallowed .

There are two ways you can add a foreign key to a DataSet : using the DataRelation object that is also used to allow navigation between tables, and adding a ForeignKeyConstraint object to the ConstraintCollection directly. In the latter case, as with unique constraints, the foreign key can be created independently and added to the collection using the Add method as shown in the following snippet, or created directly in the Add method using one of its overloaded signatures:

 Dim fk As New ForeignKeyConstraint("FK_OrderID", _   orders.Columns("OrderID"), orderDet.Columns("OrderID")) orderDet.Constraints.Add(fk) 

Note

In this release, foreign key constraints aren't populated from a data adapter in SQL Server when its MissingSchemaAction property is set to AddWithKey . This example also assumes that the dtOrders and dtOrderDet DataTable objects refer to tables within the same DataSet that contain high-level order and order detail information, respectively. Note that they are linked by the OrderID column and the constraint exists in the dtOrderDet table.

As with unique constraints, foreign key constraints can span multiple columns as long as both the parent and child tables include the columns. However, the column names in both tables must have the same data types or an InvalidOperationException will be thrown. Of course, when placing a constraint between tables that already have data, every child row must have a corresponding parent row or an ArgumentException will be thrown. In these cases, if you would like to create a foreign key constraint ”or for that matter a unique constraint ”even though the data doesn't conform to it, you can first set the EnforceConstraints property of the DataSet to False and then add the constraint. Later, after the data has been cleaned up, you can once again enable constraints by setting the property to True .

Tip

You don't need to first set the primary key of the parent table in order to create a foreign key, as is the case in most relational databases. If the primary key isn't set, a unique constraint is automatically created on the parent column.

The second way to create a foreign key is to create a DataRelation . Simply put, a DataRelation allows navigation between the parent and child tables by allowing the GetChildRows , GetParentRow , and GetParentRows methods shown in Table 4.2 to be called to navigate through the DataSet . Creating a relation has the side effect of creating a foreign key constraint as well. For example, rather than explicitly creating a foreign key constraint, as in the previous code snippet, you could create a DataRelation as follows :

 orders.Relations.Add(New DataRelation("FK_OrderID", _   orders.Columns("OrderID"), _   orderDet.Columns("OrderID"))) 

Note

Note that here the name of the relation can be specified along with the parent and child columns. The DataRelationCollection class's Add method is also overloaded to accept arrays of parent and child columns. The end result is that the DataRelationCollection objects exposed through the Relations property of the dsOrders DataSet , the ChildRelations property of the dtOrders parent DataTable , and the ParentRelations property of the dtOrderDet child DataTable are all populated along with the addition of the foreign key constraint to the ConstraintCollection of the dtOrderDet child table.

After the relation is in place, you can use it to navigate the DataSet . For example, the code in the following snippet traverses each row in the Orders table and prints each row in the OrderDetails table:

 Dim orderRow As DataRow Dim detRow As DataRow Dim detRows() As DataRow For Each orderRow In orders.Rows   Console.WriteLine(orderRow.Item("OrderID"))   Console.WriteLine(orderRow.Item("OrderDate"))   detRows = orderRow.GetChildRows("FK_OrderID", DataRowVersion.Current)   For Each detRow In detRows     Console.WriteLine("  " & detRow.Item("ISBN").ToString)     Console.WriteLine("  " & detRow.Item("Quantity").ToString)   Next Next 

Note that the GetChildRows method is overloaded and can accept either the name of the DataRelation to use in the navigation or a DataRelation object. The second argument is optional and specifies which versions of the child rows to return in the array of DataRow objects.

Note

Keep in mind that simply creating a foreign key constraint called FK_OrderID won't allow the code in the previous snippet to work. The reason is that although relations automatically create foreign key constraints, the reverse isn't true.

Cascading Changes

After a foreign key constraint has been created, you can set its AcceptRejectRule , UpdateRule , and DeleteRule properties to affect its behavior as data is modified in the parent table.

The AcceptRejectRule property can be set to either the Cascade or None value of the AcceptRejectRule enumeration. When you set the property to Cascade , each time the AcceptChanges or RejectChanges method is called on a parent row, AcceptChanges or RejectChanges is also called on all its child rows. This is convenient because often parent and child rows will be added to the DataSet at the same time and this avoids having to loop through all the child rows to call AcceptChanges . The default value of AcceptRejectRule , however, is None .

The UpdateRule and DeleteRule properties are similar and can be set to one of the four values of the Rule enumeration ( Cascade , None [the default], SetDefault , SetNull ). As in a relational database, setting either of these properties to Cascade ensures that if the parent row's primary key is changed, or if the parent row is deleted, the foreign key in the child table is likewise changed or deleted. This prevents orphaned rows from collecting in the child table. For example, the foreign key created previously should have both its DeleteRule and UpdateRule set to Cascade as follows:

 Dim fk As ForeignKeyConstraint fk = CType(orderDet.Constraints("FK_OrderID"), ForeignKeyConstraint) fk.DeleteRule = Rule.Cascade fk.UpdateRule = Rule.Cascade 

Should I Cascade?

Although cascading deletes is appropriate for some foreign keys, such as an Orders / OrderDetails relationship as discussed in this section, it isn't for others. This is particularly true of lookups where the parent table stores a set of lookup data used for normalization, such as product codes and categories. Cascading a delete in these cases can have disastrous consequences because it would delete vital data. In those cases, SetDefault or SetNull is more appropriate.

Cascading updates aren't typically used because you should strive to keep primary keys immutable, especially in the case of system-assigned keys such as GUIDs. However, there are times when natural keys (those that reflect real entities such as product codes) must be changed, and so cascading an update is called for.

The SetDefault and SetNull values can be used when the primary key needs to be changed or deleted but when the child row must remain . Using SetDefault populates the child column with the expression in its DefaultValue property, whereas SetNull simply sets the column's value to null ( Nothing ) assuming that the AllowDBNull property is set to True .

for RuBoard