Understanding the Interrelationship between Constraints and Relationships

  • Article

In an XML Schema definition language (XSD) schema, you can specify constraints (unique, key, and keyref constraints) and relationships (using the msdata:Relationship annotation). This topic explains how the constraints and relationships specified in an XML Schema are interpreted to generate the DataSet.

In general, in an XML Schema, you specify the msdata:Relationship annotation if you want to generate only relationships in the DataSet. For more information, see Generating DataSet Relations from XML Schema (XSD). You specify constraints (unique, key, and keyref) if you want to generate constraints in the DataSet. Note that the key and keyref constraints are also used to generate relationships as explained later in this topic.

Generating a Relationship from key and keyref Constraints

Instead of specifying the msdata:Relationship annotation, you can specify key and keyref constraints which are used during the XML Schema mapping process to generate not only the constraints but also the relationship in the DataSet. However, if you specify msdata:ConstraintOnly="true" in the keyref element, the DataSet will include only the constraints and will not include the relationship.

The following example shows an XML Schema that includes Order and OrderDetail elements, which are not nested. The schema also specifies key and keyref constraints.

<xs:schema id="MyDataSet"  
            xmlns:xs="http://www.w3.org/2001/XMLSchema" 
            xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

 <xs:element name="MyDataSet" msdata:IsDataSet="true">
  <xs:complexType>
    <xs:choice maxOccurs="unbounded">
      <xs:element name="OrderDetail">
       <xs:complexType>
         <xs:sequence>
           <xs:element name="OrderNo" type="xs:integer" />
           <xs:element name="ItemNo" type="xs:string" />
         </xs:sequence>
       </xs:complexType>
      </xs:element>
      <xs:element name="Order">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="OrderNumber" type="xs:integer" />
            <xs:element name="EmpNumber" type="xs:integer" />
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:choice>
  </xs:complexType>

  <xs:key name="OrderNumberKey"  >
    <xs:selector xpath=".//Order" />
    <xs:field xpath="OrderNumber" />
  </xs:key>

  <xs:keyref name="OrderNoRef" refer="OrderNumberKey">
    <xs:selector xpath=".//OrderDetail" />
    <xs:field xpath="OrderNo" />
  </xs:keyref>
 </xs:element>
</xs:schema>

The DataSet that is generated during the XML Schema mapping process includes the Order and OrderDetail tables. In addition, the DataSet includes relationships and constraints. The following example shows these relationships and constraints. Note that the schema does not specify the msdata:Relationship annotation; instead, the key and keyref constraints are used to generate the relation.

....ConstraintName: OrderNumberKey
....Type: UniqueConstraint
....Table: Order
....Columns: OrderNumber
....IsPrimaryKey: False

....ConstraintName: OrderNoRef
....Type: ForeignKeyConstraint
....Table: OrderDetail
....Columns: OrderNo
....RelatedTable: Order
....RelatedColumns: OrderNumber

..RelationName: OrderNoRef
..ParentTable: Order
..ParentColumns: OrderNumber
..ChildTable: OrderDetail
..ChildColumns: OrderNo
..ParentKeyConstraint: OrderNumberKey
..ChildKeyConstraint: OrderNoRef
..Nested: False

In the previous schema example, the Order and OrderDetail elements are not nested. In the following schema example, these elements are nested. However, no msdata:Relationship annotation is specified, therefore an implicit relation is assumed. For more information, see Mapping Implicit Relations between Nested Schema Elements. The schema also specifies key and keyref constraints.

<xs:schema id="MyDataSet"  
            xmlns:xs="http://www.w3.org/2001/XMLSchema" 
            xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

 <xs:element name="MyDataSet" msdata:IsDataSet="true">
  <xs:complexType>
    <xs:choice maxOccurs="unbounded">

      <xs:element name="Order">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="OrderNumber" type="xs:integer" />
            <xs:element name="EmpNumber" type="xs:integer" />

            <xs:element name="OrderDetail">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="OrderNo" type="xs:integer" />
                  <xs:element name="ItemNo" type="xs:string" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:choice>
  </xs:complexType>

  <xs:key name="OrderNumberKey"  >
    <xs:selector xpath=".//Order" />
    <xs:field xpath="OrderNumber" />
  </xs:key>

  <xs:keyref name="OrderNoRef" refer="OrderNumberKey">
    <xs:selector xpath=".//OrderDetail" />
    <xs:field xpath="OrderNo" />
  </xs:keyref>
 </xs:element>
</xs:schema>

The DataSet resulting from the XML Schema mapping process includes two tables:

Order(OrderNumber, EmpNumber, Order_Id)
OrderDetail(OrderNumber, ItemNumber, Order_Id)

The DataSet also includes the two relationships (one based on the msdata:relationship annotation and the second based on the key and keyref constraints) and various constraints. The following example shows the relations and constraints.

..RelationName: Order_OrderDetail
..ParentTable: Order
..ParentColumns: Order_Id
..ChildTable: OrderDetail
..ChildColumns: Order_Id
..ParentKeyConstraint: Constraint1
..ChildKeyConstraint: Order_OrderDetail
..Nested: True

..RelationName: OrderNoRef
..ParentTable: Order
..ParentColumns: OrderNumber
..ChildTable: OrderDetail
..ChildColumns: OrderNo
..ParentKeyConstraint: OrderNumberKey
..ChildKeyConstraint: OrderNoRef
..Nested: False

..ConstraintName: OrderNumberKey
..Type: UniqueConstraint
..Table: Order
..Columns: OrderNumber
..IsPrimaryKey: False

..ConstraintName: Constraint1
..Type: UniqueConstraint
..Table: Order
..Columns: Order_Id
..IsPrimaryKey: True

..ConstraintName: Order_OrderDetail
..Type: ForeignKeyConstraint
..Table: OrderDetail
..Columns: Order_Id
..RelatedTable: Order
..RelatedColumns: Order_Id

..ConstraintName: OrderNoRef
..Type: ForeignKeyConstraint
..Table: OrderDetail
..Columns: OrderNo
..RelatedTable: Order
..RelatedColumns: OrderNumber

If a keyref constraint referring to a nested table contains the msdata:IsNested="true" annotation, the DataSet will create a single nested relationship that is based on the keyref constraint and the related unique/key constraint.

See Also

Generating DataSet Relational Structure from XML Schema (XSD)