Create and Manipulate DataSets: Manipulate DataSet Relationships.
As an in-memory representation of a database, the DataSet object retains the notion of relations between tables. As you would expect, this means that a DataSet schema can also store information on keys and relationships in the DataSet that it represents.
In this section, you'll see how to add keys to a DataSet schema, and then how to create relationships betweeen multiple tables that are part of the same DataSet schema.
The DataSet schema designer allows you to create two types of keys. You can identify a field or a set of fields as the primary key for a table, or you can identify a field or a set of fields as making up a unique key. Step By Step 1.7 enables you to practice these skills.
STEP BY STEP
1.7 Creating Keys in the DataSet Schema Designer
You can create one primary key, or as many unique keys as you want, for each table in the DataSet schema designer.
The primary key identifies the field (or combination of fields) that uniquely specifies an individual row in the table. Unique keys identify other fields or combinations of fields that cannot be repeated in different records of the same table.
Deleting a Key To delete a primary key or a unique key in the DataSet schema designer, you must first click in the row containing the key to select the row. Then you can right-click on that row and select Delete Key.
One way to relate two tables in a DataSet schema is to identify common key columns and create a relationship between the tables using those columns . This is similar to the way that relational database products handle relationships. Step By Step 1.8 demonstrates this technique.
STEP BY STEP
1.8 Creating One-to-Many Relationships in the DataSet Schema Designer
You can also specify a number of optional behaviors in the Edit Relation dialog box:
To create a relationship that is used only as a constraint, but not for fetching child records, check the Create Foreign Key Constraint Only check box.
To specify the behavior of child records when the key field in the parent record is updated, select a value from the Update Rule combo box. You can choose to automatically cascade changes to the child records, to set the key field in the child records to Null, or to set the key field in the child records to its default value.
To specify the behavior of child records when a parent record is deleted, select a value from the Delete Rule combo box. You can choose to automatically cascade deletions to the child records, to set the key field in the child records to Null, or to set the key field in the child records to its default value.
To specify the behavior of child records when a change to a parent record is accepted or rejected, select a value from the Accept/Reject Rule combo box. You can choose to automatically accept changes to the child records or to leave those changes to be accepted separately.
Although one-to-many relationships are the only kind you'll find in a typical relational database, the DataSet schema designer also supports nested relationships. In a nested relationship, the child table is stored as a complex data type within the parent table. Step by Step 1.9 will help you set up a nested relationship.
STEP BY STEP
1.9 Creating Nested Relationships in the DataSet Schema Designer
Because there are two different ways to relate tables in a DataSet schema, you'll have to choose between the two. Here are some points to help you decide whether to use one-to-many or nested relationships in your DataSet schemas:
One-to-many relationships more directly represent the way that data is stored in a relational database. If your data is primarily stored in a database, this provides the most natural mapping.
Nested relationships are more natural to represent in XML. If your data is not stored in a relational database, nested relationships provide a cleaner and more succinct XML representation of the connections between tables.
If you require interoperability with other XML applications, nested relationships are more likely to be correctly interpreted.