Can MySQL Foreign Keys Reference Non-Unique Indices, and What are the Implications?

MySQL foreign key references non-unique index

Unlike traditional database theory, MySQL allows foreign key constraints to reference non-unique keys in the referenced table. This can cause ambiguity in constraint execution, especially when handling "ON DELETE CASCADE" behavior.

Understanding foreign key constraints

Foreign key constraints ensure that a row in the child table (Table1) refers to a row of valid data in the parent table (Table2). Typically, this constraint requires a one-to-one relationship between the referenced columns (column2 in both tables).

MySQL non-unique index reference

However, in MySQL, foreign keys can reference non-unique columns (column2 in Table2). This relaxes the traditional one-to-one requirement, allowing multiple child table rows to reference the same value in the parent column. Non-unique indexes exist to improve query performance when searching for values ​​that may exist in multiple rows.

Practical considerations

While MySQL supports this behavior, for practical reasons foreign keys referencing non-unique columns should be avoided. When using the "ON DELETE CASCADE" behavior, deleting a row in the parent table (Table2) may cause a cascading deletion of multiple rows in the child table (Table1), which may have unintended consequences.

MySQL Documentation Guide

The MySQL documentation explicitly advises against using foreign keys that reference non-unique keys or nullable keys. It recommends restricting foreign keys to only reference unique and non-null keys to obtain well-defined behavior.

The above is the detailed content of Can MySQL Foreign Keys Reference Non-Unique Indices, and What are the Implications?. For more information, please follow other related articles on the PHP Chinese website!