In the database structure depicted in the diagram that follows

the columns key1 & key2 make up the composite primary key of table4, but in table3 I'm able to set up a foreign key constraint that makes a reference to key1 only.

Why does MySQL allow this? Does the above database structure make no sense at all?


Quote from the documentation:

InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Although this may come as a surprise, it is "documented behaviour". This is probably one of these cases where you/we must just accept that things have been implemented in a certain manner ie InnoDB have implemented foreign keys this way.

Whether the design (in question) makes sense or not depends on the underlying business rules. There may well be situations that require such a constraint.

| improve this answer | |
  • 3
    And in another part of the docs (CREATE TABLE): "Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys." – ypercubeᵀᴹ Jun 30 '17 at 6:37

I suggest that InnoDB's semantics for a Foreign Key Constraint is simply "does this value exist in that table". Hence, non-unique and first-of-composite fit ok.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.