I remind you from Chapter 1 that, loosely speaking, a foreign key is a set of attributes in one relvar whose values are supposed to correspond to values of some candidate key—the target key—in some other relvar (or possibly in the same relvar). In the suppliers-and-parts database, for example, {SNO} and {PNO} are foreign keys in SP whose values are required to match, respectively, values of the candidate key {SNO} in S and values of the candidate key {PNO} in P. (By required to match here, I mean that if, e.g., relvar SP contains a tuple with SNO value S1, then relvar S must also contain a tuple with SNO value S1—for otherwise SP would show some shipment as being supplied by a nonexistent supplier, and the database wouldn’t be “a faithful model of reality.”)
Here now is a more precise definition:
Definition: Let R1 and R2 be relvars, not necessarily distinct, and let K be a key for R1. Let FK be a subset of the heading of R2 such that there exists a possibly empty sequence of attribute renamings on R1 that maps K into K′ (say), where K′ and FK contain exactly the same attributes (i.e., are of the same type). Further, let R2 and R1 be subject to the constraint that, at all times, every tuple t2 in R2 has an FK value that’s the K′ value for some (necessarily unique) tuple t1 in R1 at the time in question. Then FK is a foreign key (with the same degree as K); K (not K′) is the corresponding target key; the associated constraint is a referential constraint; and R2 and R1 are the referencing relvar and the corresponding referenced relvar (or target relvar), respectively, for that constraint.
As an aside, I note that the relational model as originally formulated required foreign keys to correspond not just to some key, but very specifically to the primary key, of the referenced relvar. Since we don’t insist on primary keys, however, we certainly can’t insist that foreign keys correspond to primary keys specifically, and we don’t (and SQL agrees with this position).
In the suppliers-and-parts database, to repeat, {SNO} and {PNO} are foreign keys in SP, referencing the sole candidate key—which we can therefore regard, harmlessly, as the primary key, if we want to—in S and P, respectively. Here now is a more complicated example:
As you can see, there’s a significant difference between the Tutorial D and SQL FOREIGN KEY specifications in this example. I’ll explain the Tutorial D one first. Attribute MNO denotes the employee number of the manager of the employee identified by ENO; for example, the EMP tuple for employee E3 might include an MNO value of E2, which constitutes a reference to the EMP tuple for employee E2. So the referencing relvar (R2 in the definition) and the referenced relvar (R1 in the definition) are one and the same in this example. More to the point, foreign key values, like candidate key values, are tuples; so we have to do some renaming in the foreign key specification, in order for the tuple equality comparison to be at least syntactically valid. (What tuple equality comparison? Answer: The one that’s implicit in the process of checking the foreign key constraint—recall that tuples must certainly be of the same type if they’re to be tested for equality, and “same type” means they must have the same attributes and thus certainly the same attribute names.) That’s why, in the Tutorial D specification, the target is specified not just as EMP but rather as EMP{ENO} RENAME {ENO AS MNO}. Note: The RENAME operator is described in detail in the next chapter; for now, I’ll just assume it’s self-explanatory.
Turning now to SQL: In SQL the key K in the referenced table T1 and the corresponding foreign key FK in the referencing table T2 are sequences, not sets, of columns. (In other words, key and foreign key values in SQL are rows, not tuples, and left to right column ordering is significant once again.) Let those columns, in sequence as defined within the FOREIGN KEY specification in the definition of table T2, be B1, B2, ..., Bn (for FK) and A1, A2, ..., An (for K), thus:[66]
FOREIGN KEY (B1,B2, ...,Bn) REFERENCEST1(A1,A2, ...,An)
Then columns Bi and Ai (1 ≤ i ≤ n) must be of the same type—no coercions here—but they don’t have to have the same name. That’s why the SQL specification
FOREIGN KEY ( MNO ) REFERENCES EMP ( ENO )
is sufficient as it stands, without any need for renaming.
Recommendation: Despite this last point, ensure that foreign key columns do have the same name in SQL as the corresponding key columns wherever possible (see the discussion of column naming in Chapter 3). However, there are certain situations—two of them, to be precise—in which this recommendation can’t be followed 100 percent:
Even here, however, you should at least try to follow the recommendation in spirit, as it were. For example, you might want to ensure in the second case that one of the foreign keys has the same column names as K, even though the other one doesn’t (and can’t). See Exercise 5.16 at the end of the chapter, and the answer to that exercise in Appendix F, for further discussion.
As you probably know, SQL supports not just foreign keys as such but also certain associated referential actions, such as CASCADE. Such actions can be specified as part of either an ON DELETE clause or an ON UPDATE clause. For example, the CREATE TABLE statement for shipments might include the following:
FOREIGN KEY ( SNO ) REFERENCES S ( SNO ) ON DELETE CASCADE
Given this specification, an attempt to delete a specific supplier will cascade to delete all shipments for that supplier as well.
Now, referential actions might well be useful in practice, but they aren’t part of the relational model as such. But that’s not necessarily a problem! The relational model is the foundation of the database field, but it’s only the foundation. In other words, there’s no reason why additional features shouldn’t be built on top of, or alongside, that foundation—just so long as those additions don’t violate any of the prescriptions of the model (and are in the spirit of the model and can be shown to be useful, I suppose I should add). To elaborate:
Type theory: Type theory provides the most obvious example of such an “additional feature.” We saw in Chapter 2 that “types are orthogonal to tables,” but we also saw that full and proper type support in relational systems—including support for user defined types, and perhaps even support for type inheritance—is highly desirable, to say the least. (In my own opinion, in fact, a system without such support scarcely deserves the label “relational.” See Appendix A for further discussion.)
Triggered procedures: Strictly speaking, a triggered procedure is an action (the triggered action) to be performed if a specified event (the triggering event) occurs—but the term is often used loosely to include the triggering event as well. Referential triggered actions such as ON DELETE CASCADE are just a pragmatically important example of this more general construct, in which the action is DELETE (actually the “procedure” in this particular case is specified declaratively), and the triggering event is ON DELETE.[67] No triggered procedures are prescribed by the relational model, but they aren’t necessarily proscribed either—though they would be if they led to a violation of either the model’s set level nature or The Assignment Principle, both of which they’re likely to do in practice. Note: The combination of a triggering event and the corresponding triggered action is often known just as a trigger. Recommendation: As discussed earlier, avoid use of SQL’s row level triggers, and don’t use triggers of any kind in such a way as to violate The Assignment Principle.
Recovery and concurrency: By way of a third example, the relational model has almost nothing to say about recovery and concurrency controls, but this fact obviously doesn’t mean that relational systems shouldn’t provide such controls. (Actually it could be argued that the relational model does say something about such matters implicitly, because it does rely on the DBMS to implement updates properly and not to lose data—but it doesn’t prescribe anything specific.)
One final remark to close this section: I’ve discussed foreign keys because they’re of considerable pragmatic importance, also because they’re part of the model as originally defined. But I’d like to stress the point that they’re not truly fundamental—they’re really just shorthand for certain integrity constraints that are commonly required in practice, as we’ll see in Chapter 8. (In fact, much the same could be said for candidate keys as well, but in that case the practical benefits of providing a shorthand are overwhelming.)
[66] Columns A1, A2, ..., An must be the columns named in some UNIQUE or PRIMARY KEY specification in the definition of table T1, but they don’t have to appear in that UNIQUE or PRIMARY KEY specification in the same sequence as they do in the FOREIGN KEY specification for table T2. Moreover, they, and the parentheses surrounding them, can be omitted entirely from this latter specification—but if so, then they must appear in a PRIMARY KEY specification, not a UNIQUE specification, for table T1, and they must appear in that specification in the appropriate sequence.
[67] In case you’re wondering about the SQL terminology here, ON DELETE CASCADE is a “referential triggered action” and CASCADE by itself is a “referential action.”