> Some modern systems also technically allow FK on alternate keys
As far as I can tell, all modern systems allow it, as it is part of the SQL standard that foreign keys can be either primary keys or unique indexes. Here's a brief quotation from a copy of ISO/IEC 9075-2:1999 (not the latest version) that I randomly found online:
> If the <referenced table and columns> specifies a <reference column list>, then the set of <column name>s contained in that <reference column list> shall be equal to the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table.
So it mentions unique constraints first. Then afterward it says:
> If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY.
If I'm reading this right, it means that in the base case, where you specify the column to reference, it can be any unique constraint, where a primary key is just another possible unique constraint (as all primary keys are by definition unique). And only if you don't specify the fields to reference does it then fall back to the primary key instead of a named unique constraint.
I'm not disagreeing with you entirely - it's true that often there's an assumption in database theory that primary keys are natural and foreign keys are primary keys. But this isn't a hard requirement in practice or in theory, and it partly depends on the foreign key's purpose, why you need it in the first place. This StackOverflow answer also explains it well: https://softwareengineering.stackexchange.com/a/254566
I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.
> I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.
That’s less a DB design thought and more of a “devs with little formal training in RDBMS who only want to use it as a dumb store” thought.
Use the DB to its strengths. CHECK constraints add an infinitesimal amount of overhead for writes, and guarantee that there will never be invalid data written. A bad code change could allow, however briefly, for that to occur.
As far as I can tell, all modern systems allow it, as it is part of the SQL standard that foreign keys can be either primary keys or unique indexes. Here's a brief quotation from a copy of ISO/IEC 9075-2:1999 (not the latest version) that I randomly found online:
> If the <referenced table and columns> specifies a <reference column list>, then the set of <column name>s contained in that <reference column list> shall be equal to the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table.
So it mentions unique constraints first. Then afterward it says:
> If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY.
If I'm reading this right, it means that in the base case, where you specify the column to reference, it can be any unique constraint, where a primary key is just another possible unique constraint (as all primary keys are by definition unique). And only if you don't specify the fields to reference does it then fall back to the primary key instead of a named unique constraint.
I'm not disagreeing with you entirely - it's true that often there's an assumption in database theory that primary keys are natural and foreign keys are primary keys. But this isn't a hard requirement in practice or in theory, and it partly depends on the foreign key's purpose, why you need it in the first place. This StackOverflow answer also explains it well: https://softwareengineering.stackexchange.com/a/254566
I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.