15.12. Foreign Keys

15.12. Foreign Keys
Prev	Chapter 15. Mapping Metadata	Next

Section 15.7, “Joins” introduced foreign keys as logical links between records. Most relational databases have the ability to manifest these logical links as physical database constraints. Foreign key constraints have a delete action that runs if the linked-to record is deleted, and an update action that runs if the linked-to record's primary key changes. You have a choice between the following action types:

restrict: Throw an exception if the linked-to record is deleted/updated. All databases with physical foreign keys support this action. Using this action ensures that there are no orphaned references in the database, because a record cannot be deleted or change its primary key while there are still foreign keys referencing it.
cascade: Modify the foreign key record to match the linked-to record. As a delete action, this means that if the referenced row is deleted, the row referencing it will be deleted as well. As an update action, it means that the referencing row's foreign key values will automatically change whenever the linked-to row's primary key values are modified. Not all databases support cascading foreign keys, especially cascading update action keys.
null: If the referenced row is deleted or changes its primary key, null the columns of this foreign key. Some databases do not support this action.
default: Set the columns of this foreign key to their default values if the linked-to row is deleted or changes its primary key. Some databases do not support this action.

JDOR uses foreign key declarations both at table-creation time and at runtime. If your JDOR vendor provides a tool for creating tables based on your mappings, then that tool will create physical database foreign keys corresponding to your foreign key declarations. At runtime, the JDOR implementation can use your foreign key markup to order its INSERT, UPDATE, and DELETE statements so as not to violate any foreign key constraints. For example, if you are deleting two records A and B, where A has a restrict-delete action foreign key to B, your JDOR implementation must issue the DELETE for A before the DELETE for B. Otherwise, A's foreign key will throw an exception when B is deleted, aborting the transaction. Thus, it is important that you faithfully represent your tables' foreign keys in your mapping metadata.

	Note
	If your mapping metadata specifies foreign key actions that are not supported by your database, Kodo's table creation tools fall back to supported actions automatically. If your database does not support foreign keys at all, Kodo's tools will not generate any foreign key creation SQL.

JDOR provides multiple ways to represent a physical foreign key. The easiest way is to add the delete-action attribute to any mapping element holding foreign key columns. This attribute accepts any of the foreign key action names listed above, and represents a foreign key with the restrict update action and the given delete action. Because the vast majority of foreign keys use the restrict update action, setting a delete action is an easy and concise way to represent most foreign keys. If your physical foreign key is on the columns of a relation field, set the delete-action attribute on the field element. If your foreign key is on the join columns between a subclass table and superclass table, add the delete-action attribute to the inheritance element's nested join element. The following elements can all house join columns, and therefore all accept the delete-action attribute: field, join, element, key, value.

	Note
	If your vendor's schema creation tools generate physical foreign keys on relations by default, setting the `delete-action` attribute to `none` will suppress foreign key creation.

Example 15.30. Using the delete-action Attribute

The link from a Magazine to its cover Article, the join from MAG_ARTS association table records to their owning Magazines, and the reference from each association table record to its Article are all given physical foreign keys below. With these foreign keys in place, attempting to delete any Article still referenced by a Magazine will throw an exception on flush. Deleting a Magazine will automatically clear its entries in the MAG_ARTS table (the JDOR implementation should do this anyway, but having a cascading foreign key in place is better in case other, less robust processes delete records without cleaning up after themselves).

<?xml version="1.0"?>
<orm>
    <package name="org.mag">
        <class name="Magazine" table="MAG">
            <field name="isbn">
                <column name="ISBN" jdbc-type="char" length="15"/>
            </field>
            <field name="title" column="TITLE"/>
            <field name="coverArticle" column="COVER_ID" delete-action="restrict"/>
            <field name="articles" table="MAG_ARTS">
                <join delete-action="cascade">
                    <column name="MAG_ISBN" target="ISBN"/>
                    <column name="MAG_TITLE" target="TITLE"/>
                </join>
                <element column="ART_ID" delete-action="restrict"/>
            </field>
            ...
        </class>
        <class name="Article" table="ART">
            <field name="id" column="ID"/>
            ...
        </class>    
        ...
    </package>
</orm>

The second way to represent a physical foreign key is with a contextual foreign-key element. You can nest the foreign-key element within any element that accepts a delete-action attribute. The foreign-key element goes just after any nested columns. And just as nested column elements provide more power than the column attribute, nested foreign-key elements provide more power than the delete-action attribute. The foreign-key element has the following attributes:

name: The name of the foreign key. This attribute is only used during table creation. If it is not present, the JDOR implementation will choose a suitable default name, or create an unnamed key.
delete-action: The key's delete action. Defaults to restrict.
update-action: The key's update action. Defaults to restrict.
deferred: Set this attribute to true if the actions of this key are deferred. Deferred actions wait until just before the database transaction commits before taking place. This simplifies things for the JDOR implementation, because it no longer has to carefully order its inserts, updates, and deletes to avoid foreign key constraints. Unfortunately, many databases do not support deferred constraints.

The foreign-key element also allows nested extension elements.

Here is the previous example, modified to use foreign-key elements rather than delete-action attributes. This version also specifies the names of the keys, and makes some of them deferred.

Example 15.31. Using Contextual Foreign Key Elements

<?xml version="1.0"?>
<orm>
    <package name="org.mag">
        <class name="Magazine" table="MAG">
            <field name="isbn">
                <column name="ISBN" jdbc-type="char" length="15"/>
            </field>
            <field name="title" column="TITLE"/>
            <field name="coverArticle" column="COVER_ID">
                <foreign-key name="COV_ART_FK" deferred="true"/>
            </field>
            <field name="articles" table="MAG_ARTS">
                <join column="MAG_ID">
                    <foreign-key name="OWNER_MAG_FK" delete-action="cascade"/>
                </join>
                <element column="ART_ID">
                    <foreign-key name="MAG_ART_FK" deferred="true"/>
                </element>
            </field>
            ...
        </class>
        <class name="Article" table="ART">
            <field name="id" column="ID"/>
            ...
        </class>    
        ...
    </package>
</orm>

Prev	Up	Next
15.11. Field Mapping	Home	15.13. Indexes