1

I've been researching whether it's valid to have a foreign key that refereces a partial primary key of another table.

From several sources (see below), it seems like it's not valid, however, it works perfectly fine using MySQL.

Is this behavior specific to MySQL, or is it valid to have a foreign key that references a partial primary key in general?

I would appreciate a link to some documentation that confirm any claims.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

https://www.w3schools.com/sql/sql_foreignkey.asp

Partial foreign key reference. A foreign key must refer to an entire primary key, and not just part of it

https://www.dataversity.net/primary-key-and-foreign-key-errors-to-avoid/

Dale K
  • 25,246
  • 15
  • 42
  • 71
Shuzheng
  • 11,288
  • 20
  • 88
  • 186

1 Answers1

3

This behaviour is storage engine specific in mysql. As mysql manual on foreign key constraints says:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 15.6.2.1, “Clustered and Secondary Indexes”).

NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL.

Despite innodb allowing this behaviour, I do not suggest you to go down this route because questions the exact relationship between the two tables. Always link to the entire primary or unique key.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you. What is meant by an *explicit unique key*? Is that just a synonym for *primary key*? And why is **NDB** mentioned? Never heard of it, only familiar with InnoDB, the database engine of MySQL. – Shuzheng Sep 18 '20 at 07:50
  • 1
    No, unique key is not the same as the PK. See https://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key InnoDB is the **default** table engine for MySQL, not the only engine it has. NDB is another table engine. It is documented in the MySQL manual, so pls check it out, if you want to know more about it. – Shadow Sep 18 '20 at 11:32