外键可以引用非唯一索引吗? [英] Can a foreign key reference a non-unique index?

问题描述

我认为外键意味着单行必须引用单行，但我正在查看一些表，但事实并非如此.表 1 的列 1 在表 2 的列 2 上具有外键约束，但表 2 中有许多记录在列 2 中具有相同的值.column2 上也有非唯一索引.这是什么意思?外键约束是否仅仅意味着至少一个记录必须存在于正确的列中并具有正确的值?我认为这意味着必须有一个这样的记录(不确定空值如何适应图片，但我目前不太关心这个).

I thought a foreign key meant that a single row must reference a single row, but I'm looking at some tables where this is definitely not the case. Table1 has column1 with a foreign key constraint on column2 in table2, BUT there are many records in table2 with the same value in column2. There's also non-unique index on column2. What does this mean? Does a foreign key constraint simply mean that at least one record must exist with the right values in the right columns? I thought it meant there must be exactly one such record (not sure how nulls fit in to the picture, but I'm less concerned about that at the moment).

更新:显然，这种行为是特定于 MySQL 的，这是我使用的，但我在最初的问题中没有提到它.

update: Apparently, this behavior is specific to MySQL, which is what I was using, but I didn't mention it in my original question.

推荐答案

来自 MySQL 文档:

InnoDB 允许外键约束引用非唯一键.这是标准 SQL 的 InnoDB 扩展.

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

然而，避免在引用表的非唯一列上使用外键是有实际原因的.也就是说，在这种情况下，ON DELETE CASCADE"的语义应该是什么?

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?

文档进一步建议:

对非唯一键或包含 NULL 值的键的外键引用的处理没有明确定义 (...) 建议您使用仅引用 UNIQUE(包括 PRIMARY)和 NOT NULL 键的外键.

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.

这篇关于外键可以引用非唯一索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！