What happens with the previous code snippet if myParent is deleted? What happens if the parent's Pk is changed from 2 to 10? What happens if another DataRow object is added to dt2, where its parent is set to 99 and there is no parent with a Pk of 99?
As it turns out, creating a DataRelation object also creates constraints to deal with these issues. It's important to understand that the creation of constraints is optional; the following line of code does not create the constraints.
Visual Basic
Dim rel As New DataRelation("dt1dt2", parentPk, childFk, False)
C#
DataRelation rel = new DataRelation("dt1dt2", parentPk, childFk, false);
The DataRelation object is used only for navigation between DataTable objects; the constraint provides enforcement of data integrity by ensuring that a child row has a parent when the child is added and that changing or deleting a parent does not orphan any children.
In this example, where the constraints are created automatically, if myParent is deleted, the child DataRow objects are deleted automatically. This is called a cascading delete, and it is turned on by default. If the parent's Pk field is changed from 2 to 10, the Fk field in the child DataRow objects is changed to 10 as well. This is called a cascading update, and it is also turned on by default. If an attempt is made to add a child DataRow object that has no parent, an InvalidConstraintException is thrown. Chapter 1 covers the constraint in more detail.
In most scenarios, it is appropriate to create relationships that create constraints with the default settings for cascading update and delete, but there are some scenarios where you want to create a relationship but you don't want the default constraints. For example, if you are holding data that is somewhat historical in nature, you might want to allow orphaned DataRow objects to exist. Here are a couple of examples:
In an order entry application, if a customer places an order for a widget, the widget information is placed into the order item table. If the widget becomes obsolete, you might want to delete the widget from the product table. You don't want to delete child DataRow objects that exist in the order item table, so you might create a relationship without creating the constraints.
In many applications, you might keep audit records for every insert, change, and delete that is sent to the database. The audit record identifies the change as well as the person who made the change. If a person is removed from the database, you would not want the audit records to be removed, so you might create a relationship without creating the constraints.
In both of these scenarios, you need the ability to delete a DataRow object without causing a cascading delete. Normally the child DataRow object contains only an identifier of the parent DataRow object, but in these cases, you also need to copy more information into the child DataRow object because the parent DataRow might not exist later. For the widget, you need to copy the name and price of the widget to the order item table. For the audit record, you need to copy the person's name to the audit table.
Another scenario in which you might not want the default constraint setting is when business rules dictate that a parent DataRow object cannot be deleted when child DataRow objects exist. For example, you might have a business rule dictating that you cannot delete a customer who has outstanding invoices. You can imagine the consequences of a cascading delete of the invoices if you delete a customer. To best address this situation, you should create the constraints but set the DeleteRule property to None, which causes an exception to be thrown if you try to delete a customer who has outstanding invoices.
Disconnected Data Access Scenario Revisited
Joe is a traveling salesperson who needs to maintain a list of customers and orders for his territory. Joe takes a subset of the data with him on the road, where he can modify the data and store it to disk. When he returns to the office, he sends his changes to the main database server and retrieves updated information. If any concurrency conflicts arise, Joe is prompted to select the correct data.
This is the same scenario presented in Chapter 5, but here you look at how to retrieve all of Joe's customer data, make changes, and send all of the changes back to the database. The database schema has been tweaked a bit in this chapter, and it is shown in Figure 6-1.
Figure 6-1: Modified order entry database schema
Notice that the TblSalesperson/TblCustomer and TblProduct/TblOrderItem relationships are represented using a dashed line, which is an indication of a relationship in the database that is unenforced. This means that salespeople and products can be removed without causing foreign key constraint errors.
