Declaring Foreign-Key Constraints

Declaring Foreign-Key Constraints

A second important kind of constraint on a database schema is that values for certain attributes must make sense. That is, an attribute like presC# of relation Studio is expected to refer to a particular movie executive. The implied "referential integrity" constraint is that if a studio's tuple has a certain certificate number c in the presC# component, then c is the certificate of a real movie executive. In terms of the database, a "real" executive is one mentioned in the MovieExec relation. Therefore, there must be some MovieExec tuple that has c in the cert# attribute.

In SQL we may declare an attribute or attributes of one relation to be a foreign key, referencing some attribute(s) of a second relation (possibly the same relation). The implication of this declaration is twofold:

1. The referenced attribute(s) of the second relation must be declared UNIQUE or the PRIMARY KEY for their relation. Otherwise, we cannot make the foreign-key declaration.

2. Values of the foreign key appearing in the first relation must also appear in the referenced attributes of some tuple. More specifically, let there be a foreign-key F that references set of attributes G of some relation. Assume a tuple t of the first relation has non-NULL values in all the attributes of F; call the list of t's values in these attributes t[F]. Then in the referenced relation there must be some tuple s that agrees with t[F] on the attributes
G. That is, s[G] = t[F].

As for primary keys, we have two ways to declare a foreign key.

a) If the foreign key is a single attribute we may follow its name and type by a declaration that it "references" some attribute (which must be a key - primary or unique) of some table. The form of the declaration is



b) On the other hand, we may append to the list of attributes in a CREATE TABLE statement one or more declarations stating that a set of attributes is a foreign key. We then give the table and its attributes (which must be a key) to which the foreign key refers. The form of this declaration is:



Example 1 : Assume we wish to declare the relation

  Studio(name, address, presC#)

whose primary key is name and which has a foreign key presC# that references cert# of relation

  MovieExec(name, address, cert#, netWorth)

We may declare presC# directly to reference cert# as follows:

CREATE TABLE Studio (
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
presC# INT REFERENCES MovieExec(cert#)
);

An alternative form is to add the foreign key declaration separately, as

CREATE TABLE Studio (
name CHAR(3O) PRIMARY KEY,
address VARCHAR(255),
presC# INT,
FOREIGN KEY (presC#) REFERENCES MovieExec(cert#)
);

Notice that the referenced attribute, cert# in MovieExec, is a key of that relation, as it must be. The meaning of either of these two foreign key declarations is that whenever a value appears in the presC# component of a Studio tuple, that value must also appear in the cert# component of some MovieExec tuple. The one exception is that, should a particular Studio tuple have NULL as the value of its presC# component, there is no requirement that NULL appear as the value of a cert# component (actually, cert# is a primary key and therefore cannot have NULL's anyway).


Tags