Database Reference
In-Depth Information
Based on the definition of a foreign key, the following consequential points should
be noted:
1.
The foreign key and the referenced primary key must be
defined on the same domain. However, the attribute-names
can be different (some implementations of SQL may require
that they be identical).
2.
The foreign key need not be a component of the primary key
for the host (holding) relation (in which case nulls may be
accepted, but only with the understanding that they will be
subsequently updated).
3.
If relations Rn, R(n-1), R(n-2) .... R1 are such that
Rn → R(n-1) → R(n-2) → .... R2 → R1 then the chain Rn to R1
forms a
referential path
.
4.
A relation can be both referenced and referencing. Consider
the referential path R3 → R2 → R1. In this case, R2 is both a
referenced and a referencing relation.
5.
We can have a self-referencing relation. Consider for example,
the relation
Employee
{Emp#, EmpName .... MgrEmployee#}
with primary key [Emp#]where attribute
MgrEmp#
is a
foreign key defined on the relation
Employee
. In this case we
have a self-referencing relation.
6.
More generally, a referential cycle exists when there is a
referential path from Rn to itself: Rn → R(n-1) → .... R1 → Rn
7.
Foreign-to-primary-key matches are said to be the “glue”
that holds the database together. As you will see, relations are
joined based on foreign keys.
Deletion of Referenced Tuples
Now that we have established the importance of foreign keys, we need to address a
question: How will we treat deletion of referenced tuples? Three alternatives exist.
•
Restrict
deletion to tuples that are not referenced.
•
Cascade
deletion to all referencing tuples in referencing relations.
•
Allow the deletion but
nullify
all referencing foreign keys in
referencing relations.
The third approach is particularly irresponsible, as it could quite quickly put the
integrity of the database in question, by introducing
orphan records
. Traditionally, DBMS
suites implement the restriction strategy (and for good reasons). The cascading strategy
has been surfacing in contemporary systems, as an optional feature. It must be used with
much care, as it is potentially dangerous when used without discretion.
