6.11

In SQL, foreign key constraints can reference only the primary key attributes of the referenced relation or other attributes declared to be a superkey using the unique constraint. As a result, total participation constraints on a many-to-many relationship set (or on the “one” side of a one-to-many relationship set) cannot be enforced on the relations created from the relationship set, using primary key, foreign key, and not null constraints on the relations.

  1. Explain why.

  2. Explain how to enforce total participation constaints using complex check constraints or assertions (see Section 4.4.8). (Unfortunately, these features are not supported on any widely used database currently.)

  1. For the many-to-many case, the relationship set must be represented as a separate relation that cannot be combined with either participating entity. Now, there is no way in SQL to ensure that a primary-key value occuring in an entity \(E1\) also occurs in a many-to-many relationship \(R\), since the corresponding attribute in \(R\) is not unique; SQL foreign keys can only refer to the primary key or some other unique key.

Similary, for the one-to-many case, there is no way to ensure that an attribute on the one side appears in the relation corresponding to the many side, for the same reason.

  1. Let the relation \(R\) be many-to-one from entity \(A\) to entity \(B\) with \(a\) and \(b\) as their respective primary keys. We can put the following check constraints on the “one” side relation \(B\):
CONSTRAINT total_part CHECK (b IN (SELECT b FROM A));
SET CONSTRAINTS total_part DEFERRED;

Note that the constraint should be set to deferred so that it is only checked at the end of the transaction; otherwise if we insert a \(b\) value in \(B\) before it is inserted in \(A\), the constraint would be violated, and if we insert it in \(A\) before we insert it in \(B\), a foreign-key violation would occur.