Manipulating DataSet Relationships
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 object 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.
Adding Keys to a 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 lets you practice these skills.
|
1.7 Creating Keys in the DataSet Schema Designer
Ensure that the DataSet schema file that you edited in Step-by-Step 1.6 is open in the DataSet schema designer. Select the key tool in the Toolbox. Drag the key from the Toolbox and drop it on the CustomerID element within the Customers table. This will open the Edit Key dialog box. Name the key CustomersPrimaryKey. Select CustomerID in the Fields list, if it is not already selected. Check the DataSet Primary Key check box, as shown in Figure. Click OK to create the key. A key icon appears next to the CustomerID element.
Select the key tool in the Toolbox. Drag the key from the Toolbox and drop it on the CompanyName element within the Customers table. This will open the Edit Key dialog box. Name the key CompanyNameUniqueKey. Select CompanyName in the Fields list, if it is not already selected. Click OK to create the key. A key icon appears next to the CompanyName element. Figure shows the Customers table with both keys created.
|
You can create one primary key, and as many unique keys as you like, 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.
EXAM TIP
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-to-Many Relationships
A one-to-many relationship is the most common type of relationship in a relational database. In a one-to-many relationship, a record in TableA can have more than one matching record in TableB, but a record in TableB has, at most, one matching record in TableA.
One way to relate two tables in a DataSet schema is to identify common key columns and then use those columns to create a relationship between the tables. This is similar to the way that relational database products handle relationships. Step-by-Step 1.8 demonstrates this technique.
|
1.8 Creating One-to-Many Relationships in the DataSet Schema Designer
Ensure that the DataSet schema file that you edited in Step-by-Step 1.7 is open in the DataSet schema designer. Select the Relation tool in the Toolbox. Drag the Relation from the Toolbox and drop it on the Orders table. This opens the Edit Relation dialog box. Name the relation CustomersOrders. Select Customers as the parent element and Orders as the child element. Select CustomersPrimaryKey as the key to use. Select CustomerID as both the key field and the foreign key field. Leave all other options at their default values. Figure shows this dialog box.
Click OK to create the relation. The DataSet schema designer draws a relation object and connecting lines between the two tables, as shown in Figure.
|
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.
Nested Relationships
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 helps you set up a nested relationship.
|
1.9 Creating Nested Relationships in the DataSet Schema Designer
Ensure that the DataSet schema file that you edited in Step-by-Step 1.8 is open in the DataSet schema designer. Select the Order Details table. Drag the Order Details table and drop it on top of the Orders table. This creates a relationship between the Orders table and the Order Details table, as shown in Figure.
Note that in this case, there's no explicit relationship object; the line between the two tables does not have a diamond on it. Instead, the Order Details table has been added as a complex type to the Orders table.
|
Because there are two different ways to relate tables in a DataSet schema, you 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.
To create a primary key or a unique key in a DataSet schema, drag and drop the key tool from the Toolbox to an XML element. To create a one-to-many relationship, drag and drop the relation tool from the Toolbox to the relationship's child table. To create a nested relationship, drag and drop the child table to the parent table.
|
|
