Handling Other Forms of Relationship - Firebird

Referential constraints can be applied to other forms of relationship apart from the optional one-to-many form described so far:

  • One-to-one
  • Many-to-many
  • Self-referencing one-to-many (nested or tree relationships)
  • Mandatory variants of any form of relationship

One-to-One Relationship

Optional one-to-one structures can be valuable where an entity in your data model has a large number of distinct attributes, only some of which are accessed frequently. It can save storage and page reads dramatically to store occasional data in optional “peer” relations that share matching primary keys.

A one -to -one relationship is similar to a one-to-many relationship, insofar as it links a foreign key to a unique key. The difference here is that the linking key needs to be unique to enforce the one-to-one relationship —to allow, at most, one dependent row per parent row.

It is usual to double up the use the primary key column(s) of the “peer” table as the foreign key to the “parent.”

The effect of this double usage is to cause two mandatory indexes to be created on the primary key column of the peer table: one for the primary key and one for the foreign key. The FK index is stored as if it were non-unique.

In versions 1.0.x and 1.5, the optimizer is quirky about this doubling up and ignores the peer table’s primary index. For example,

ignores the primary key index of the peer and produces this plan:

With a “thin” key, such as the one used in the example, the impact on performance may not be severe. With a composite key, the effect may be serious, especially if there will be multiple joins involving several parent -to-peer one-to-one relations. It should at least make you consider surrogating the primary keys of one-to-one structures.

Many-to-Many Relationship

In this interesting case shown in Figure, our data model shows us that each row in Table may have relationships with multiple rows in TableB, while each row in TableB may have multiple relationships with rows in Table.

Many-to-many relationship

Many-to-many relationship

As modeled, this relationship gives rise to a condition known as a circular reference. The proposed foreign key in TableB references the primary key of TableA, which means that the Table row cannot be created if there is no row in TableA with a matching primary key. However, for the same reason, the required row cannot be inserted into TableA if there is no matching primary key value in Table.

Dealing with a Circular Reference

If your structural requirements dictate that such a circular reference must exist, it can be worked around. Firebird allows a foreign key value to be NULL —provided the column is not made non-nullable by another constraint—because NULL, being a “non-value,” does not violate the rule that the foreign key column must have a matching value in the referenced parent column. By making the FK on one table nullable, you can insert into that table and create the primary key that the other table requires:

This is the workaround:

Clearly, this model is not without potential problems. In most systems, keys are generated, not supplied by applications. To ensure consistency, it becomes a job for all client applications inserting to these tables to know the value of both keys in both tables within a single transaction context. Performing the entire operation with a stored procedure would reduce the dependence of the relationship on application code.

Using an Intersection Table

Generally, it is better practice to resolve many-to-many relationships by adding an ntersection table. This special structure carries one foreign key for each table in the many-to-many relationship. Its own primary key (or a unique constraint) is a composite of the two foreign keys. The two related tables intersected by it do not have foreign keys relating to one another at all.

This implementation is easy to represent in applications. Before Insert and Before Update triggers on both tables take care of adding intersection rows when required. Figure illustrates how the intersection table resolves many -to -many relationships.

Resolution of a many-to-many relationship

Resolution of a many-to-many relationship

This is how to implement it:

Self-Referencing Relationships

If your model has an entity whose primary key refers to a foreign key located in the same entity, you have a self-referencing relationship, as shown in Figure.

Self-referencing relationship

Self-referencing relationship

This is the classic tree hierarchy, where any member (row) can be both parent and child—that is, it can have “child” rows dependent on it and, at the same time, it can depend on another member (row). It needs a CHECK constraint or Before Insert and Before Update triggers to ensure that a PARENT_ID never points to itself.

If your business rules require that parents must exist before children can be added, you will want to use a value (e.g., –1) as the root node of the tree structure. The PARENT_ID should be made NOT NULL and defaulted to your chosen root value. The alternative is to leave PARENT_ID as nullable, as in the following example, and use NULL as the root.

In general, custom triggers for Before Insert and Before Update will be required for trees that will be nested to more than two levels. For consistency in trees with a NULL root node, it is important to ensure that constraint actions do not create orphan children unintentionally.

About Tree Structures

Much more can be said about designing tree structures. It is a challenging topic in relational database design that stretches standard SQL to its boundaries. Unfortunately, it is beyond the scope of this guide.

Mandatory Relationships

A mandatory, or obligatory relationship is one that requires that a minimum of one referencing (child) row exist for each referenced (parent) row. For example, a delivery note structure (a header with customer and delivery address information) would be illogical if it were permitted to have a header row without any referencing item lines.

It is a common beginner mistake to assume that a NOT NULL constraint on the child will make a one-to-many relationship mandatory. It does not, because the FOREIGN KEY constraint operates only in the context of an instantiated dependency. With no referencing row, the nullability of the foreign key is irrelevant to the issue.

A mandatory relationship is one place where user-defined trigger constraints must be added to extend referential integrity. Firebird SQL does not provide a “mandatoriness” constraint. It can take some fancy logic at both the client and the server to ensure that events will occur in the right sequence to meet both the referential constraint and the mandatoriness requirements. It will involve both insert and delete triggers, since the logic must enforce the “minimum of one child” rule not only at creation time, but also when child rows are deleted.


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

Firebird Topics