Creating Constraints

 

Creating Constraints

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.

image from book

Visual Basic

Dim rel As New DataRelation("dt1dt2", parentPk, childFk, False) 
image from book

image from book

C#

DataRelation rel = new DataRelation("dt1dt2", parentPk, childFk, false); 
image from book

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 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.

image from book
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.

image from book
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.

image from book

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net