CODEWITHSUNDEEP
mysqlsql-server
Shuzheng
Shuzheng

Reputation: 13850

Is it valid to have a foreign key that references a partial primary of another table?

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/

Upvotes: 1

Views: 636

Answers (1)

Shadow
Shadow

Reputation: 34231

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.

Upvotes: 3

Related Questions