SQL and Relational Theory, 2nd Edition

MORE ON FOREIGN KEYS

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