[SOLVED] Example of when you should use a foreign key that points to a candidate key, not a primary key?

Example of when you should use a foreign key that points to a candidate key, not a primary key?

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".

What are examples of why you might choose a candidate key and not the primary key?

Solution

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. A CK is a superkey that contains no smaller superkey (unique column set). 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 the analogue of a superkey. An SQL PK declaration enforces a UNIQUE NOT NULL constraint, so in general it too declares the analogue of a superkey, not CK--a smaller UNIQUE NOT NULL or PK (superkey) can be declared inside it, but a CK cannot contain a smaller superkey. And an SQL FK declaration declares the analogue of what we could call a foreign superkey: the referencing column list references a column list in a PK or UNIQUE (not necessarily NOT NULL) declaration.

Does an empty SQL table have a superkey? Does every SQL table?

A relational FK constraint says that subrows under certain columns of the source table must appear as a CK subrow of the referenced table. An SQL FK constraint says that NULL-free subrows under certain columns of the source table must appear as a UNIQUE (possibly PK) subrow of the referenced table. (Details depend on SQL MATCH mode; typically only the default mode is supported.) 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.)