19.2. Foreign-Key tag within dbforms-config.xml

Foreign key infomation within config file has to be expressed using the foreign-key tag that has to be nested within the table tag belonging to the referencing tables.

19.2.1. Here is an excerpt from the DTD:

 
   <!ELEMENT foreign-key (reference+)>

   <!ATTLIST foreign-key
       foreignTable    CDATA #REQUIRED
       name            CDATA #REQUIRED
       visibleFields   CDATA #IMPLIED
       format          CDATA #IMPLIED
       displayType (select|radio|none) "none"
   >


   <!ELEMENT reference EMPTY>

   <!ATTLIST reference
       local           CDATA #REQUIRED
       foreign         CDATA #REQUIRED
   >

 

19.2.2.  Let's say you have created a database containing tables:

 
   create table 
   customer
       (cno integer not null primary key,
       first_name char(20) not null,
       last_name  char(20) not null
       phone      char(20) not null,
       ...
       )

   create table 
   order
       (orderno integer not null primary key,
       cust_no integer not null,
       order_date date not null,
       ...
       constraint customer_known_in_order
           foreign key (cust_no)
           references customer(cno)
       )
  
      

19.2.3. Then within dbforms-config.xml you could write:

 
   <table name="customer">
       <field name="cno" fieldType="integer" isKey="true"/>
       <field name="first_name" fieldType="char" size="20"/>
       <field name="last_name" fieldType="char" size="20"/>
       <field name="phone" fieldType="char" size="20"/>
       ...
   <table>

   <table name="order">
       <field name="cno" fieldType="integer" isKey="true"/>
       <field name="cust_no" fieldType="integer"/>
       <field name="order_date" fieldType="date"/>
       ...


       <foreign-key foreignTable="customer"

                 name="customer_in_order"

                 displayType="select"

                 visibleFields="first_name,last_name,phone"

                 format="%s %s (Phone %s)" >


       <reference local="cust_no" foreign="cno"/>

       </foreign-key>
   </table>
      
  • the foreign-key has to be nested within the referencing table

  • attributes:

    • foreignTable: name of referenced table (required)

    • name: name of this constraint (required, used for a faster look-up)

    • displayType: This is a suggestion how to present this reference within an automatically generated page

      • select: create a select tag

      • radio : create a set of radio fields

      • none : ignore reference, do nothing (default if not set)

    • visibleFields: which columns of the referenced table shall be presented to the user? This gets directly passed to corresponding attribute within tableData tag, see docs there

    • format: which format pattern shall be used to format the visibleFields? See docs for tag tableData for details.

  • nested tags reference: models pairs of columns that take part in the reference:

    • local: name of the referencing column

    • foreign: name of the referenced column

      If a key consists of more than one column, there will be more than one nested tag, e.g. if the customer is just unique within an area, we'd have:

         <foreign-key foreignTable="customer" ...>
              
           <reference local="area_id" foreign="area_id"/>
              
           <reference local="cust_no" foreign="cno"/>
          </foreign-key>