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

问题描述

我认为一个外键意味着一行必须引用一行，但我正在看一些表，这是绝对不是这样的情况。 Table1的column1在table2中的column2上有一个外键约束，但是在table2中有许多记录在column2中有相同的值。第2列还有非唯一索引。这是什么意思？外键约束是否仅仅意味着至少有一条记录必须存在右列中的正确值？我认为这意味着必须有一个这样的记录（不知道如何适合图片，但我现在不那么担心）。

更新：显然，这种行为是特定于MySQL，这是我正在使用的，但我没有提到它在我原来的问题。

解决方案

div>

从 MySQL文档：

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

然而，有一个实际的原因是为了避免引用非唯一列的外键表。也就是说，在这种情况下，ON DELETE CASCADE的语义应该是什么？

文档进一步建议：

lockquote

对包含NULL值的非唯一键或键的外键引用的处理没有定义好（...）建议使用只引用UNIQUE（包括PRIMARY）和NOT NULL键的外键。

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).

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

解决方案

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.

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