Export (0) Print
Expand All
13 out of 17 rated this helpful - Rate this topic

Tables, Columns, Keys, and Constraints in XML Schemas

Visual Studio .NET 2003

Relationships in XML Schemas involve creating individual tables with columns. If your schema uses a one-to-many relationship, then creating your XML Schema also involves designating the common columns as keys and keyrefs, creating relationships between them, and applying the appropriate constraints. Nested relationships in an XML Schema also involve creating tables with columns, except single column definitions may contain the complete definitions of related tables.

This topic will explain how typical database constructs such as tables, columns, primary keys, foreign keys, unique keys, and constraint rules are represented in XML Schemas and datasets.

Visual representation of relational data in the XML Designer

d8wa0tw7.vbkeyedrwt(en-us,VS.71).gif

Tables

Tables in XML Schemas are represented by complex elements, and the columns are represented as sub-elements and attributes of that element. The name of the table is equal to the name of the element. The names of the columns are equal to the names of the sub-elements and attributes that make up the complex element. For more information, see Complex Types in XML Schemas.

For example, the following XML Schema could represent a table named Customers. The elements CustomerID through PostalCode represent the columns that make up the table. The CustomerID column is defined as the primary key.

Note   Notice how the primary key definition is hierarchically outside the
Customers
element definition, yet inside of a higher-level
NewDataSet
element. If there were other tables defined in this dataset, they would be nested within the
NewDataSet
element as well. This ensures that the primary key column is unique within the scope of the entire dataset (as opposed to being unique within the scope of just one record in the
Customers
table).

The following is a visual representation of the Customers table in the XML Designer:

d8wa0tw7.vbcustomerstable(en-us,VS.71).gif

The following example shows the XML code that represents the Customers table in a dataset named NewDataSet:

<xs:element name="NewDataSet" msdata:IsDataSet="true">
 <xs:complexType>
  <xs:choice maxOccurs="unbounded">
   <xs:element name="Customers">
    <xs:complexType>
     <xs:sequence>
      <xs:element name="CustomerID" type="xs:string"/>
      <xs:element name="CompanyName" type="xs:string"/>
      <xs:element name="ContactName" type="xs:string" minOccurs="0"/>
      <xs:element name="ContactTitle" type="xs:string" minOccurs="0"/>
      <xs:element name="Address" type="xs:string" minOccurs="0"/>
      <xs:element name="City" type="xs:string" minOccurs="0"/>
      <xs:element name="Region" type="xs:string" minOccurs="0"/>
      <xs:element name="PostalCode" type="xs:string" minOccurs="0"/>
     </xs:sequence>
    </xs:complexType>
   </xs:element>
  </xs:choice>
</xs:complexType>
  <xs:unique name="NewDataSetKey1" msdata:PrimaryKey="true">
   <xs:selector xpath=".//Customers"/>
   <xs:field xpath="CustomerID"/>
  </xs:unique>
</xs:element>

Columns

In the previous example, note how the individual columns (such as CustomerID, CompanyName, and ContactName) are declared as simple elements within a complex-type definition. The two elements without a minOccurs attribute are required columns, whereas the elements with the minOccurs="0" attribute are optional columns. The attribute type="string" refers to a simple type that is built-in to all XML Schemas. More importantly, note how the table is defined as an in-line unnamed complexType (the columns) within the NameOfTable element (the table). The following example shows how to define a table and its columns.

<xs:element name="NameOfTable">
 <xs:complexType>
   <xs:sequence>
     <xs:element name="RequiredColumn" type="xs:string"/>
     <xs:element name="OptionalColumn" type="xs:string" minOccurs="0"/>
   </xs:sequence>
 </xs:complexType>
</xs:element>
Note   When you create a column definition in a schema, you can create it as either an element or an attribute. The choice you make depends primarily on your own application requirements. If you are defining the schema to match a specification for sharing data with another application, then the specification will dictate your choice.

Primary Keys and Unique keys

Primary Keys

Primary keys ensure columns contain unique values that cannot be NULL. In the XML Designer, adding a key that does not have the Primary key option selected creates a unique key. Unique constraints ensure that columns do not contain duplicate values.

Based on the example earlier in this section, the following example shows how to define a CustomerID column as a primary key in an XML Schema:

<xs:key name="NewDataSetKey1" msdata:PrimaryKey="true">
   <xs:selector xpath=".//Customers"/>
   <xs:field xpath="CustomerID"/>

</xs:key>

Note   By dragging a key from the XML Schema tab of the Toolbox onto an element and setting the appropriate fields, the previous code would be automatically generated by the XML Designer. For more information, see Creating Primary and Unique Keys in XML Schemas.

Unique Keys

Unique constraints can allow null values, whereas primary key constraints do not allow null values. Another consideration when deciding whether to use a primary or unique key is that tables can have multiple unique constraints but only one primary key.

Using the same example from earlier in this section, if you had an application that required that the CompanyName column must be unique, you would impose a unique key constraint on that column. The following example shows how to define a CompanyName column as a unique key in an XML Schema:

<xs:unique name="NewDataSetKey2">
   <xs:selector xpath=".//Customers" />
   <xs:field xpath="CompanyName" />
</xs:unique>
Note   By dragging a key from the XML Schema tab of the Toolbox onto an element and setting the appropriate fields, the previous code would be automatically generated by the XML Designer. For more information, see Creating Primary and Unique Keys in XML Schemas.

KeyRefs

A keyref defines the column on the many side of a one-to-many relationship, much like a foreign key in database terminology. An element that has been designated as a keyref has a relationship with the key or unique element in another table. Using the previous example, assume there is a table named Orders in the same dataset that has a column named CustomerID. You can create a keyref named CustomerOrders that creates the relationship between the Customers table and the Orders table.

<xs:keyref name="CustomerOrders" refer="NewDataSetKey1">
   <xs:selector xpath=".//Orders" />
   <xs:field xpath="CustomerID" />
</xs:keyref>
Note   By dragging a Relation from the XML Schema tab of the Toolbox onto an element and setting the appropriate fields, the previous code would be automatically generated by the XML Designer. For more information, see Creating DataRelation Objects with the XML Designer.

Referential Integrity

When creating a relationship with the Edit Relation dialog box, you can select the rules that affect what happens to related data. These rules are called referential integrity. When updating or deleting a record in a primary key column, there can be many records in another table that reference the primary key. The following table describes the different constraint rules that can be applied using the Edit Relation dialog box.

RuleDescription
CascadeDeletes or updates related rows.
SetNullSets values in related rows to DBNull.
SetDefaultSets values in related rows to DefaultValue.
NoneNo action taken on related rows.

See Also

Relational Data in XML Schemas | XML Schemas and Data | XML Schemas and the XML Designer | Creating XML Schemas and Datasets | XML Files | Introduction to Data Access with ADO.NET | Introduction to Datasets

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.