15.12. Foreign Keys
Prev | Chapter 15. Mapping Metadata | Next

## 15.12. Foreign Keys

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 `Magazine`s, 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 `column`s. 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