Can a foreign key reference a non-unique index?

by

From MySQL documentation:

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

However, there is a pratical reason to avoid foreign keys on non-unique columns of referenced table. That is, what should be the semantic of “ON DELETE CASCADE” in that case?

The documentation further advises:

The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined (…) You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

More Related Contents:

  1. MySQL Cannot Add Foreign Key Constraint
  2. Required to join 2 tables with their FKs in a 3rd table
  3. MySQL – Conditional Foreign Key Constraints
  4. How do I use on delete cascade in mysql?
  5. How to create relationships in MySQL
  6. Is it possible to reference one column as multiple foreign keys?
  7. Foreign key constraints: When to use ON UPDATE and ON DELETE
  8. MySQL pivot row into dynamic number of columns
  9. Rank function in MySQL
  10. Count the number of occurrences of a string in a VARCHAR field?
  11. Find nearest latitude/longitude with an SQL query
  12. Groupwise maximum
  13. Entity-Attribute-Value Table Design
  14. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  15. Filter by COUNT(*)?
  16. How to select rows that have current day’s timestamp?
  17. MySQL concatenation operator
  18. SQL Group By with an Order By
  19. How create json format with group-concat mysql?
  20. MySql – Way to update portion of a string?
  21. Using reserved words in column names
  22. MySQL INNER JOIN select only one row from second table
  23. Storing IPv6 Addresses in MySQL
  24. Insert/ Update random date in MySQL
  25. How to remove constraints from my MySQL table?
  26. What is the difference between SQL and MySQL? [closed]
  27. how to use a like with a join in sql?
  28. Get total hours worked in a day mysql
  29. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys
  30. Update multiple rows with multiple ‘where’ clauses for each individual row

Leave a Comment