XSD keyref help

[Eric Dalquist <edalquist@unicon.net>]

I'm working on an XSD and may have run into something that isn't 
possible with my current data format.

In the attached tables.xml I have something along the following lines:

    <table>
        <name>UP_TABLE_A</name>
        <columns>
            <column>
                <name>AID</name><type>INTEGER</type>
                <desc>generated id</desc>
            </column>
        </columns>
        <primary-key>AID</primary-key>
    </table>
   
    <table>
        <name>UP_TABLE_B</name>
        <desc>Holds portlet definitions</desc>
        <columns>
            <column>
                <name>BID</name><type>INTEGER</type>
                <desc>generated</desc>
            </column>
            <column>
                <name>AID_FK</name><type>INTEGER</type>
                <desc>foreign key from TABLE_A</desc>
            </column>
            <column>
                <name>MISC</name><type>VARCHAR</type><param>255</param>
            </column>
        </columns>
        <primary-key>ID</primary-key>
        
<foreign-key><column>AID_FK</column><ref-table>UP_TABLE_A</ref-table><ref-column>AID</ref-column></foreign-key>
    </table>


I have constraints in the XSD to ensure that references to other 
elements, such as the primary-key are valid. The one I haven't figured 
out how to do yet is to ensure the ref-column is correct. The constraint 
is that the value ref-column must be a column name in the table 
specified by ref-table.

I've attached my current XML and XSD if they'll help.

Let me know if there is a way to do this or if I just can't do it.

Thank you,
    Eric Dalquist
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema";>
    
    <!-- 
     | Defines the root element of the XML document.
     +-->
    <xs:element name="tables" type="tablesType">
        <!-- 
         | Defines a named key using the name element of the table element which
         | is the only element that can be a child as defined by tablesType. This
         | ensures the table names are unique and can be used to provide referential
         | integrety in other elements
         +-->
        <xs:key name="tableNameKey">
            <xs:selector xpath="table"/>
            <xs:field xpath="name"/>
        </xs:key>
        
        <!-- 
         | Ensures foreign-key/ref-table elements have values corresponding to actual
         | tables defined in the tables.xml document.
         +-->
        <xs:keyref name="foreignKeyRefTableRef" refer="tableNameKey">
            <xs:selector xpath="table/foreign-key"/>
            <xs:field xpath="ref-table"/>                
        </xs:keyref>
    </xs:element>

    <!-- 
     | Defines the root table element. Specifies that any number of table elements
     | may be specified.
     +-->
    <xs:complexType name="tablesType">
        <xs:sequence>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="table" type="tableType">
                <!--
                 | Defines a named key using the column name elements. This ensures that the column
                 | names will be unique for this table and can be used to provide referential
                 | integrety with other elements.
                 +-->
                <xs:key name="columnNameKey">
                    <xs:selector xpath="columns/column"/>
                    <xs:field xpath="name"/>
                </xs:key>
                
                <!-- 
                 | Ensures each primary-key element references a real column in this table
                 +-->
                <xs:keyref name="primaryKeyColumnRef" refer="columnNameKey">
                    <xs:selector xpath="primary-key"/>
                    <xs:field xpath="."/>
                </xs:keyref>
                
                <!-- 
                 | Ensures the local column references for each foreign key exists in this table
                 +-->
                <xs:keyref name="forgeigKeyColumnRef" refer="columnNameKey">
                    <xs:selector xpath="foreign-key"/>
                    <xs:field xpath="column"/>
                </xs:keyref>
                
                <!-- 
                 | Ensures each index column exists in this table
                 +-->
                <xs:keyref name="indexColumnRef" refer="columnNameKey">
                    <xs:selector xpath="index/column"/>
                    <xs:field xpath="."/>
                </xs:keyref>
                
                <!-- 
                 | Ensures each unique element references a real column in this table
                 +-->
                <xs:keyref name="uniqueColumnRef" refer="columnNameKey">
                    <xs:selector xpath="unique"/>
                    <xs:field xpath="."/>
                </xs:keyref>
                
                <!-- 
                 | Ensures each not-null element references a real column in this table
                 +-->
                <xs:keyref name="notNullColumnRef" refer="columnNameKey">
                    <xs:selector xpath="not-null"/>
                    <xs:field xpath="."/>
                </xs:keyref>
            </xs:element>
        </xs:sequence>
    </xs:complexType>  
    
    <!-- 
     | Defines the table element. This contains all the meta-data, and contstraints along
     | with the columns element.
     +-->
    <xs:complexType name="tableType">
        <xs:sequence>
            <xs:element minOccurs="1" maxOccurs="1"         name="name"        type="xs:string"/>
            <xs:element minOccurs="0" maxOccurs="1"         name="desc"        type="xs:string"/>
            <xs:element minOccurs="1" maxOccurs="1"         name="columns"     type="columnsType"/>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="primary-key" type="xs:string"/>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="foreign-key" type="forgeinKeyType"/>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="index"       type="indexType"/>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="unique"      type="xs:string"/>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="not-null"    type="xs:string"/>
        </xs:sequence>
    </xs:complexType>
    
    <!--
     | Defines the columns element. Contains some number of columns.
     +-->
    <xs:complexType name="columnsType">
        <xs:sequence>
            <xs:element minOccurs="0" maxOccurs="unbounded" name="column" type="columnType"/>
        </xs:sequence>
    </xs:complexType>
    
    <!--
     | Defines the column element. Contains the descriptive data needed for a table column.
     +-->
    <xs:complexType name="columnType">
        <xs:sequence>
            <xs:element minOccurs="1" maxOccurs="1" name="name"  type="xs:string"/>
            <xs:element minOccurs="1" maxOccurs="1" name="type"  type="xs:string"/>
            <xs:element minOccurs="0" maxOccurs="1" name="param" type="xs:string"/>
            <xs:element minOccurs="0" maxOccurs="1" name="desc"  type="xs:string"/>
        </xs:sequence>
    </xs:complexType>
    
    <!--
     | Defines the forgein-key element. Contains a column which should reference a column
     | in the same table, the forgein table and the key column in the forgein table. 
     +-->
    <xs:complexType name="forgeinKeyType">
        <xs:sequence>
            <xs:element minOccurs="1" maxOccurs="1" name="column"     type="xs:string"/>
            <xs:element minOccurs="1" maxOccurs="1" name="ref-table"  type="xs:string"/>
            <xs:element minOccurs="1" maxOccurs="1" name="ref-column" type="xs:string"/>
        </xs:sequence>
    </xs:complexType>
    
    <!--
     | Defines the index element. Contains 1 to N columns to be used for the index.
     +-->
    <xs:complexType name="indexType">
        <xs:sequence>
            <xs:element minOccurs="1" maxOccurs="unbounded" name="column" type="xs:string"/>
        </xs:sequence>
    </xs:complexType>
</xs:schema>
<?xml version="1.0"?>
<!--
    * Please keep all table and column names as uppercase.
    * Please limit all table and column names to 20 characters or less.
    * Please use the following abbreviations for table and column names:

        Name             Abbreviation
        ================ ============
        Class            CLS
        Definition       DEF
        Deployment       DEP
        Identifier       ID
        Name             NM
        Portlet          PORT
        Preference       PREF
        Sequence         SEQ
        Value            VL
        Attribute        ATTR
        Date             DT
        Entity           ENT
        Window           WIN
        Application      APPL

    $Revision: 1.16 $
-->
<tables xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:noNamespaceSchemaLocation="tables.xsd">

    <table>
        <name>UP_SEQ</name>
        <desc>Keeps track of database IDs</desc>
        <columns>
            <column>
                <name>SEQ_NM</name><type>VARCHAR</type><param>64</param>
                <desc>Sequence name</desc>
            </column>
            <column>
                <name>SEQ_VL</name><type>INTEGER</type>
                <desc>Sequence value</desc>
            </column>
        </columns>
        <primary-key>SEQ_NM</primary-key>
    </table>
    
    <table>
        <name>UP_PORT_APPL_DEF</name>
        <desc>Holds references to portlet application deployment ids</desc>
        <columns>
            <column>
                <name>ID</name><type>INTEGER</type>
                <desc>generated id</desc>
            </column>
            <column>
                <name>PORT_APPL_DEP_NM</name><type>VARCHAR</type><param>255</param>
                <desc>portlet application deployment id (from portlet.xml file)</desc>
            </column>
        </columns>
        <primary-key>ID</primary-key>
        <unique>PORT_APPL_DEP_NM</unique>
    </table>
    
    <table>
        <name>UP_PORT_DEF</name>
        <desc>Holds portlet definitions</desc>
        <columns>
            <column>
                <name>ID</name><type>INTEGER</type>
                <desc>Portlet definition ID - primary key</desc>
            </column>
            <column>
                <name>PORT_DEP_NM</name><type>VARCHAR</type><param>255</param>
                <desc>Portlet deployment ID</desc>
            </column>
            <column><name>APPROVER_ID</name><type>INTEGER</type>
                <desc>user id of approver</desc>
            </column>
            <column><name>APPROVAL_DT</name><type>DATE</type>
                <desc>Definition approval date</desc>
            </column>
            <column><name>PUBLISHER_ID</name><type>INTEGER</type>
                <desc>user id of publisher</desc>
            </column>
            <column><name>PUBLISH_DT</name><type>DATE</type>
                <desc>Definition publish date</desc>
            </column>
            <column><name>PORT_APPL_DEF_ID</name><type>INTEGER</type>
                <desc>ID of the portlet application this definition is a part of</desc>
            </column>
        </columns>
        <primary-key>ID</primary-key>
        <foreign-key><column>PORT_APPL_DEF_ID</column><ref-table>UP_PORT_APPL_DEF</ref-table><ref-column>ID</ref-column></foreign-key>
        <unique>PORT_DEP_NM</unique>
        <not-null>PORT_DEP_NM</not-null>
    </table>
    
    <table>
        <name>UP_PORT_PREF_NM</name>
        <desc>Holds portlet preference names</desc>
        <columns>
            <column>
                <name>PREF_ID</name><type>INTEGER</type>
                <desc>Portlet preference ID</desc>
            </column>
            <column>
                <name>PREF_TYPE</name><type>VARCHAR</type><param>32</param>
                <desc>The type of preference (entity, definition, etc...)</desc>
            </column>
            <column>
                <name>OWNER_ID</name><type>INTEGER</type>
                <desc>ObjectID of the owner of the preference</desc>
            </column>
            <column>
                <name>PREF_NM</name><type>LONGVARCHAR</type>
                <desc>Portlet definition preference name</desc>
            </column>
            <column>
                <name>READ_ONLY</name><type>BIT</type>
                <desc>Read only property</desc>
            </column>
        </columns>
        <primary-key>PREF_ID</primary-key>
        <primary-key>PREF_TYPE</primary-key>
        <primary-key>OWNER_ID</primary-key>
        <not-null>PREF_NM</not-null>
        <not-null>READ_ONLY</not-null>
    </table>    
    
    <table>
        <name>UP_PORT_PREF_VL</name>
        <desc>Holds portlet preference values</desc>
        <columns>
            <column>
                <name>PREF_ID</name><type>INTEGER</type>
                <desc>Portlet preference ID - primary key</desc>
            </column>
            <column><name>PREF_VL</name><type>LONGVARCHAR</type>
                <desc>Portlet definition preference value</desc>
            </column>
        </columns>
        <foreign-key><column>PREF_ID</column><ref-table>UP_PORT_PREF_NM</ref-table><ref-column>PREF_ID</ref-column></foreign-key>
        <index><column>PREF_ID</column><column>PREF_VL</column></index>
        <index><column>PREF_ID</column></index>
    </table>
    
    <table>
        <name>UP_USER</name>
        <desc>Holds user ID and name</desc>
        <columns>
            <column>
                <name>UID</name><type>INTEGER</type>
                <desc>User unique ID</desc>
            </column>
            <column>
                <name>USER_NM</name><type>VARCHAR</type><param>64</param>
                <desc>user name</desc>
            </column>
        </columns>
        <primary-key>UID</primary-key>
    </table>
    
    <table>
        <name>UP_USER_ATTR</name>
        <desc>Holds user attributes</desc>
        <columns>
            <column>
                <name>USER_ID</name><type>INTEGER</type>
                <desc>User unique ID</desc>
            </column>
            <column>
                <name>ATTR_NM</name><type>VARCHAR</type><param>50</param>
                <desc>attribute name</desc>
            </column>
            <column>
                <name>ATTR_VL</name><type>VARCHAR</type><param>255</param>
                <desc>attribute value</desc>
            </column>
        </columns>
        <primary-key>USER_ID</primary-key>
        <primary-key>ATTR_NM</primary-key>
        <foreign-key><column>USER_ID</column><ref-table>UP_USER</ref-table><ref-column>USER_ID</ref-column></foreign-key>
    </table>
    
    <table>
        <name>UP_PORT_APPL_ENT</name>
        <desc></desc>
        <columns>
            <column>
                <name>ID</name><type>INTEGER</type>
                <desc>generated id</desc>
            </column>
            <column>
                <name>USER_ID</name><type>INTEGER</type>
                <desc>User ID</desc>
            </column>
            <column>
                <name>PORT_APPL_DEF_ID</name><type>INTEGER</type>
                <desc>fk to application definition</desc>
            </column>
        </columns>
        <primary-key>ID</primary-key>
        <foreign-key><column>USER_ID</column><ref-table>UP_USER</ref-table><ref-column>UID</ref-column></foreign-key>
        <foreign-key><column>PORT_APPL_DEF_ID</column><ref-table>UP_PORT_APPL_DEF</ref-table><ref-column>ID</ref-column></foreign-key>
    </table>
    
    <table>
        <name>UP_PORT_ENT</name>
        <columns>
            <column>
                <name>ID</name><type>INTEGER</type>
                <desc>generated id</desc>
            </column>
            <column>
                <name>USER_ID</name><type>INTEGER</type>
                <desc>User ID</desc>
            </column>
            <column>
                <name>PORT_APPL_ENT_ID</name><type>INTEGER</type>
            </column>
            <column>
                <name>PORT_DEF_ID</name><type>INTEGER</type>
            </column>
        </columns>
        <primary-key>ID</primary-key>
        <foreign-key><column>USER_ID</column><ref-table>UP_USER</ref-table><ref-column>ID</ref-column></foreign-key>
        <foreign-key><column>PORT_APPL_ENT_ID</column><ref-table>UP_PORT_APPL_ENT</ref-table><ref-column>ID</ref-column></foreign-key>
        <foreign-key><column>PORT_DEF_ID</column><ref-table>UP_PORT_DEF</ref-table><ref-column>ID</ref-column></foreign-key>
    </table>
    
    <table>
        <name>UP_PORT_WIN</name>
        <columns>
            <column>
                <name>ID</name><type>INTEGER</type>
                <desc>generated id</desc>
            </column>
            <column>
                <name>USER_ID</name><type>INTEGER</type>
                <desc>User ID</desc>
            </column>
            <column>
                <name>PORT_ENT_ID</name><type>INTEGER</type>
            </column>
            <column>
                <name>PORT_MODE</name><type>VARCHAR</type><param>32</param>
            </column>
            <column>
                <name>WIN_STATE</name><type>VARCHAR</type><param>32</param>
            </column>
        </columns>
        <primary-key>ID</primary-key>
        <foreign-key><column>USER_ID</column><ref-table>UP_USER</ref-table><ref-column>ID</ref-column></foreign-key>
        <foreign-key><column>PORT_ENT_ID</column><ref-table>UP_PORT_ENT</ref-table><ref-column>ID</ref-column></foreign-key>
    </table>
    
</tables>