The FOREIGN KEY Constraint - Firebird

A foreign key is a column or set of columns in one table that corresponds in exact order to a column or set of columns defined as a PRIMARY KEY or a UNIQUE constraint in another table. In its simplest form, it implements an optional one-to-many relationship.

The standard entity-relationship model depicts a simple one-to-many relationship between two entities as shown in Figure.

Entity-relationship model

Entity-relationship model

If we implement this model as two tables, PARENT and CHILD, then rows in the CHILD table are dependent on the existence of a linking row in PARENT. Firebird’s FOREIGN KEY (FK) constraint enforces this relationship in the following ways:

  • It requires that the value presented in the FK column of the referencing table, CHILD (CHILD.PARENT_ID), must be able to be linked to a matching value present in the referenced unique key (in this case, the primary key) of PARENT (PARENT.ID).
  • By default, it disallows a row in PARENT to be deleted, or to have its linking unique key value changed to a different value, if dependent CHILD rows exist.
  • It must implement the relationship that was intended at the time the reference was created or the last time it was updated.
  • By default, it allows the FK column to be null. Since it is impossible to link null to anything, such child rows are orphans —they have no parent.

Implementing the Constraint

In order to implement the referential constraint, certain prerequisites must be attended to. In this section, we follow through a very simple example. If you are developing in an existing, complex environment, where SQL privileges are in effect, then you may need to be concerned about the REFERENCE privilege. It is introduced in a separate section later in this chapter.

The Parent Structure

It is necessary to start with the parent table and implement a controlling unique key to which the dependent table will link. This is commonly the primary key of the parent table, although it need not be. A foreign key can link to a column or group that has been constrained using the UNIQUE constraint. For present purposes, we will use the primary key:

The Child Structure

In the child structure, we need to include a column, PARENT_ID, that exactly matches the primary key of the parent in type and size (and also column order, if the linkage involves multiple columns):

The next thing to do is declare the relationship between the child and the parent by means of a FOREIGN KEY constraint.

Syntax for Defining a FOREIGN KEY

The syntax pattern for a referential integrity definition is as follows:

Defining our foreign key:

Firebird stores the constraint FK_CHILD_PARENT and creates an ordinary index on the column(s) named in the FOREIGN KEY argument. In Firebird 1.5, the index will be named FK _CHILD _PARENT as well, unless you used the optional USING clause to assign a different name to the index. In Firebird 1.0.x , the index name will be INTEG_nn
(where nn is a number).

The two tables are now engaged in a formal referential integrity constraint. We can add new rows to PARENT without restriction:

However, there are restrictions on CHILD. We can do this:

Because the nullable column PARENT_ID was omitted from the column list, NULL is stored there. This is allowed under the default integrity rules. The row is an orphan.

However, we get a constraint error if we try to do this:

violation of FOREIGN KEY constraint "FK_CHILD_PARENT" on table "CHILD"

There is no row in PARENT having a PK value of 2, so the constraint disallows the insert.

Both of the following are allowed:

Now, the PARENT row with ID=1 has two child rows. This is the classic master-detail structure —an uncomplicated implementation of the one-to-many relationship. To protect the integrity of the relationship, the default rules will disallow this:

DELETE FROM PARENT WHERE ID = 1;

Action Triggers to Vary Integrity Rules

Obviously, integrity rules take effect whenever some change in data occurs that affects the relationship. However, the default rules do not suit every requirement. We may want to override the rule that permits child rows to be created as orphans or to be made orphans by having their foreign key set to null in an operation. If it is a problem for our business rules that a parent row cannot be deleted if it has dependent child rows, we may want Firebird to take care of the problem automatically. Firebird’s SQL language can oblige, through its optional automatic action triggers:

Automatic Action Triggers

Firebird provides the optional standard DML events ON UPDATE and ON DELETE, along with a range of action options to vary the referential integrity rules. Together, the DML event and the automatic behavior specified by the action option form the action trigger —the action to be taken in this dependent table when updating or deleting the referenced key in the parent. The actions defined include cascading the change to associated foreign table(s).

Action Trigger Semantics

NO ACTION

Because this is the default action trigger, the keyword can be—and usually is—omitted. The DML operation on the parent’s PK leaves the foreign key unchanged and potentially causes the operation on the parent to fail.

ON UPDATE CASCADE

In the dependent table, the foreign key corresponding to the old value of the primary key is updated to the new value of the primary key.

ON DELETE CASCADE

In the dependent table, the row with the corresponding key is deleted.

SET NULL

The foreign key corresponding to the old parent PK is set to NULL—the dependent rows become orphans. Clearly, this action trigger cannot be applied if the foreign key column is non-nullable.

SET DEFAULT

The foreign key corresponding to the old parent PK is set to its default value. There are some “gotchas” about this action that are important to know about:

  • The default value is the one that was in effect at the time the FOREIGN KEY constraint was defined. If the column’s default changes later, the original default for the FK’s SET DEFAULT action does not follow the new default—it remains as the original.
  • If no default was ever declared explicitly for the column, then its default is implicitly NULL. In this case, the SET DEFAULT behavior will be the same as SET NULL.
  • If the default value for the foreign key column is a value that has no corresponding PK value in the parent, then the action trigger will cause a constraint violation.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics