One of the mainstays of a relational database is the ability for one
record to refer to another. This allows relational databases to join
related records together to retrieve large chunks of data efficiently.
A record A typically references a record B
by setting some of its columns to the same values as
B's primary key columns. The columns
in A that mirror primary key columns in B
are called foreign key columns. Most
relational databases can enforce several special behaviors for foreign
keys, which we explore in
Section 15.12, “Foreign Keys”. For now, though, it is
sufficient to remember that foreign keys are sets of columns that act
as a logical reference to another record by storing that record's
primary key values.
![[Note]](img/note.gif) | Note |
|---|---|
In Kodo, logical foreign key columns do not have to link to primary key columns. You can link any columns. You can also express joins in which some columns must have specific constant values. See the Reference Guide's Section 7.6, “Non-Standard Joins” for details on Kodo's support for non-standard joins. |
Consider the two tables above. Table T1 has
a primary key column PK, two foreign key columns
FK1 and FK2, and a standard data
column DATA. Table T2 has
two primary key columns PK1 and
PK2, foreign key column FK, and a data
column. The dotted arrows indicate that T1's
foreign key columns link to T2's primary key
columns, and T2's foreign key column links to
T1's primary key column. A foreign key's table
is called the source table, and the table it
links to is called the target table.
By matching the data in the foreign key and primary key columns, you
can see that row R1 in T1
references row R2 in T2 and
vice versa. Conversely, row R2 in
T1 references row R1 in
T2 and vice versa. The solid arrows depict these
cross-referencing relationships.
In JDOR, you specify a foreign key column's target primary key column
with the column element's target
attribute. So, assuming we're mapping something in a class using
the T1 table, the link from T1
to T2 becomes:
<class name="..." table="T1">
<...>
<column name="FK1" target="PK1"/>
<column name="FK2" target="PK2"/>
</...>
</class>
Note that we did not qualify the column names above with the containing table name. The foreign key column names are not qualified because the foreign key columns are in the parent class' table. The target primary key column names are not qualified because JDOR defaults the target table based on the context of the mapping. For example, when you map a relation field, JDOR assumes the target table is the table of the related class.
A standard forward foreign key like the
one above maps the target table records
referenced by the current source table record.
You can, however, also map an inverse foreign key.
An inverse foreign key maps all the source table
records referencing the current target table
record. Inverse foreign keys only apply to mappings from
the target table - in this case, T2. Thus, you
must qualify the foreign key column names with their source table name:
<class name="..." table="T2">
<...>
<column name="T1.FK1" target="PK1"/>
<column name="T1.FK2" target="PK2"/>
<.../>
</class>
Now let's shift our focus to the link from T2 to
T1 through the T2.FK foreign
key column. This is easy enough to represent in mapping metadata:
<class name="..." table="T2">
<...>
<column name="FK" target="PK"/>
<.../>
</class>
We can, however, simplify this even further. When the target
table's primary key consists of a single column, you can omit the
target attribute. JDOR assumes that the target
is the lone primary key column of the target table (which is itself
defaulted based on the mapping context):
<class name="..." table="T2">
<...>
<column name="FK"/>
<.../>
</class>
Recall from the previous section that when you only need to specify
a column name, you can use the enclosing element's column
attribute in place of a nested column
element. This allows us to be even more concise:
<class name="..." table="T2">
<... column="FK"/>
</class>
JDOR provides one other significant shortcut not seen here. When
the foreign key column name is the same as the target primary key
column name, you do not have to set the target
attribute.
Foreign key columns do not have to reference records in other tables. Often, a table will use foreign keys to itself.
As you can see, R1 links to R2
through the FK foreign key column. The mapping
is straightforward:
<class name="..." table="T1">
<...>
<column name="FK" target="PK"/>
<.../>
</class>
Or, after applying available shortcuts:
<class name="..." table="T1">
<... column="FK"/>
</class>
You may be wondering how to represent an inverse self-join. Because the source and target tables are the same, the naïve approach results in a mapping that looks identical to a forward join, which is obviously incorrect:
<class name="..." table="T1">
<...>
<column name="FK" target="PK"/>
<.../>
</class>
The answer is simple and surprisingly consistent. When we created
an inverse foreign key mapping between T1 and
T2 above, we had to fully qualify the foreign
key column names. To represent an inverse self join, just use the
same trick:
<class name="..." table="T1">
<...>
<column name="T1.FK" target="PK"/>
<.../>
</class>
Though the foreign key column is in the parent class' table and wouldn't normally require qualification, using the qualified name serves as a hint to the JDOR implementation that this is an inverse join.
JDOR allows you to specify a target primary key field name in place
of a target column with the target-field
attribute. This is simply an object-oriented shortcut
for targetting the named field's column. For example:
<class name="A" table="T1">
<field name="relationToB">
<column name="FK1" target-field="id1"/>
<column name="FK2" target-field="id2"/>
</field
</class>
<class name="B" table="T2">
<field name="id1" column="PK1"/>
<field name="id2" column="PK2"/>
</class>
Is equivalent to:
<class name="A" table="T1">
<field name="relationToB">
<column name="FK1" target="PK1"/>
<column name="FK2" target="PK2"/>
</field
</class>
<class name="B" table="T2">
<field name="id1" column="PK1"/>
<field name="id2" column="PK2"/>
</class>
You can fully-qualify a target field name with its class, just as you can fully-qualify a target column name with its table.
| Note |
|---|---|
Target fields are useful when you rely on Kodo's mapping tools to create the schema, and you want to supply mapping hints without having to name the target columns. The Reference Guide Details Kodo's mapping tools in Section 7.1, “Forward Mapping”. Also, Kodo does not require that target fields be primary key fields. See Section 7.6, “Non-Standard Joins” in the Reference Guide. |
