Can Foreign Keys Reference Non-Unique Indexes?

Foreign key constraints on non-unique indexes in database design

In database design, foreign key constraints establish a relationship between two tables, ensuring that rows in one table refer to valid rows in the other table. However, it is generally accepted that foreign keys must reference a unique index, meaning there is a one-to-one relationship between rows.

However, in certain database management systems such as MySQL, foreign keys can reference non-unique indexes. This deviates from standard SQL interpretation.

According to the MySQL documentation, foreign keys can reference non-unique keys, which is an extension to the standard. However, it is important to note that using non-unique columns for foreign key references can cause real problems.

For example, if the referenced table column contains duplicate values ​​and the "ON DELETE CASCADE" constraint is applied, the behavior of cascading deletes becomes ambiguous. Therefore, it is strongly recommended to use foreign keys that refer to unique (including primary keys) and non-null keys.

In summary, while it is possible to create foreign keys on non-unique indexes in some database systems, it is recommended to avoid doing so due to potential inconsistencies and ambiguities in data integrity maintenance.

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