Example of when you should use a foreign key that

Question

From my reading, I understand what makes a good primary key, what a foreign key is and what a candidate key is.

I've read in several different books and sources that:

• A foreign key must point to a candidate key (or primary)
• A foreign key almost always points to a primary key

The authors of the sources always say something along the lines of, "while foreign keys can point at a candidate key (not primary) they seem to".

Are there any examples of why you might choose a candidate key and not the primary key?

Thank you

Answer 1

Primary keys (PKs) have no role in relational theory. (Eg integrity or normalization.) A PK is just some candidate key (CK) that you decided to call "primary". A foreign key (FK) references a CK. When one table has more than one CK and another table references one that just doesn't happen to be the PK you should still declare a FK. A DBMS can use PK declarations for other purposes.

In SQL a UNIQUE NOT NULL declaration declares a superkey. A CK is a superkey that contains no smaller superkey. An SQL PK declaration declares a UNIQUE NOT NULL constraint, so it actually declares a superkey. And an SQL FK declaration actually declares a foreign superkey: the referencing column list references a column list in a PK or UNIQUE NOT NULL declaration.

A FK or foreign superkey says that the subrow of the source table must appear as a subrow of the referenced table. When that is so & not already a consequence of previous FK declarations, declare a FK.

Eg: A table of chemical elements would reasonably have three CKs: name, symbol and atomic number. Only one can be PK. Yet whenever any of the columns appears in another table FKs should be declared for them. If more than one appear simultaneously referring to the same element, they should form a composite FK. (And FK declarations for each are redundant.)