MySQL 外键可以引用非唯一索引吗？

Can a foreign key reference a non-unique index?

提问by allyourcode

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

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

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，这是我使用的，但我没有在我原来的问题中提到它。

回答by Hobbes

From MySQL documentation:

从MySQL 文档：

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

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

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?

但是，避免在引用表的非唯一列上使用外键是有实际原因的。也就是说，在这种情况下，“ON DELETE CASCADE”的语义应该是什么？

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.

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

回答by chaos

Your analysis is correct; the keys don't have to be unique, and constraints will act on the set of matching rows. Not usually a useful behavior, but situations can come up where it's what you want.

你的分析是正确的；键不必是唯一的，约束将作用于匹配的行集。通常不是一种有用的行为，但情况可能会出现在您想要的地方。

回答by Evan

Yes, you can create foreign keys to basically any column(s) in any table. Most times you'll create them to the primary key, though.

是的，您基本上可以为任何表中的任何列创建外键。不过，大多数情况下，您会将它们创建为主键。

If you do use foreign keys that don't point to a primary key, you might also want to create a (non-unique) index to the column(s) being referenced for the sake of performance.

如果您确实使用了不指向主键的外键，为了提高性能，您可能还想为被引用的列创建一个（非唯一）索引。

Depends on the RDBMS you're using. I think some do this for you implicitly, or use some other tricks. RTM.

取决于您使用的 RDBMS。我认为有些人会暗中为您执行此操作，或者使用其他一些技巧。RTM。

回答by Walter Mitty

When this happens, it usually means that two foreign keys are being linked to each other. Often the table that would contain the key as a primary key isn't even in the schema.

发生这种情况时，通常意味着两个外键正在相互链接。通常，包含键作为主键的表甚至不在模式中。

Example: Two tables, COLLEGES and STUDENTS, both contain a column called ZIPCODE.

示例： COLLEGES 和 STUDENTS 这两个表都包含一个名为 ZIPCODE 的列。

If we do a quick check on

如果我们做一个快速检查

SELECT * FROM COLLEGES JOIN STUDENTS ON COLLEGES.ZIPCODE = STUDENTS.ZIPCODE

We might discover that the relationship is many to many. If our schema had a table called ZIPCODES, with primary key ZIPCODE, it would be obvious what's really going on.

我们可能会发现这种关系是多对多的。如果我们的模式有一个名为 ZIPCODES 的表，主键是 ZIPCODE，那么真正发生的事情就很明显了。

But our schema has no such table. Just because our schema has no such table doesn't mean that such data doesn't exist, however. somewhere, out in USPO land, there is just such a table. And both COLLEGES.ZIPCODE and STUDENTS.ZIPCODE are references to that table, even if we don't acknowledge it.

但是我们的模式没有这样的表。然而，仅仅因为我们的模式没有这样的表并不意味着这样的数据不存在。在某个地方，在 USPO 的土地上，就有这样一张桌子。并且 COLLEGES.ZIPCODE 和 STUDENTS.ZIPCODE 都是对该表的引用，即使我们不承认它。

This has more to do with the philosophy of data than the practice of building databases, but it neatly illustrates something fundamental: the data has characteristics that we discover, and not only characteristics that we invent. Of course, what we discover could be what somebody else invented. That's certainly the case with ZIPCODE.

这更多地与数据哲学有关，而不是建立数据库的实践，但它巧妙地说明了一些基本原理：数据具有我们发现的特征，而不仅仅是我们发明的特征。当然，我们发现的可能是别人发明的。ZIPCODE 肯定就是这种情况。

回答by bortzmeyer

PostgreSQL also refuses this (anyway, even if it is possible, it does not mean it is a good idea):

PostgreSQL 也拒绝了这一点（无论如何，即使有可能，也不意味着这是一个好主意）：

essais=> CREATE TABLE Cities (name TEXT, country TEXT);
CREATE TABLE
essais=> INSERT INTO Cities VALUES ('Syracuse', 'USA');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Syracuse', 'Greece');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Paris', 'France');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Aramits', 'France');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Paris', 'USA');
INSERT 0 1

essais=> CREATE TABLE People (name TEXT, city TEXT REFERENCES Cities(name));
ERROR:  there is no unique constraint matching given keys for referenced table "cities"

回答by Stefan Steiger

Necromancing.
As others already said, you shouldn't reference a non-unique key as foreign key.
But what you can do instead (without delete cascade danger) is adding a check-constraint (at least in MS-SQL).
That's not exactly the same as a foreign key, but at least it will prevent the insertion of invalid/orphaned/dead data.

死灵法术。
正如其他人已经说过的，您不应该将非唯一键引用为外键。
但是你可以做的（没有删除级联危险）是添加一个检查约束（至少在 MS-SQL 中）。
这与外键不完全相同，但至少它会防止插入无效/孤立/死数据。

See here for reference (you'll have to port the MS-SQL code to MySQL syntax):
Foreign Key to non-primary key

请参阅此处以供参考（您必须将 MS-SQL 代码移植到 MySQL 语法）：
Foreign Key to non-primary key

Edit:
Searching for the reasons for the downvote, according to Mysql CHECK Constraint, MySQL doesn't really support CHECK constraints.
You can define them in your DDL query for compatibility reasons, but they are just ignored...

编辑：
根据Mysql CHECK Constraint 搜索拒绝投票的原因，MySQL 并不真正支持 CHECK 约束。
出于兼容性原因，您可以在 DDL 查询中定义它们，但它们只是被忽略了...

But as mentioned there, you can create a BEFORE INSERTand BEFORE UPDATEtrigger, which will throw an error when the requirements of the data are not met, which is basically the same thing, except that it's an even bigger mess.

但正如那里提到的，你可以创建一个BEFORE INSERT和BEFORE UPDATE触发器，当数据的要求不满足时，它会抛出一个错误，这基本上是一样的，除了它是一个更大的混乱。

As to the question:

至于问题：

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.

我认为外键意味着单行必须引用单行，但我正在查看一些表，但事实并非如此。

In any sane RDBMS, this is true.
The fact that this is possible in MySQL is just one more reason why
MySQL is an in-sane RDBMS.
It may be fast, but sacrificing referential integrity and data quality on the altar of speed is not my idea of a quality-rdbms.
In fact, if it's not ACID-compliant, it's not really a (correctly functioning) RDBMS at all.

在任何健全的 RDBMS 中，这都是正确的。
这在 MySQL 中是可能的这一事实只是
MySQL 是一个健全的 RDBMS 的另一个原因。
它可能很快，但在速度的祭坛上牺牲参照完整性和数据质量并不是我对质量 rdbms 的想法。
事实上，如果它不符合 ACID，那么它根本就不是一个（正常运行的）RDBMS。

回答by cdonner

What database are we talking about? In SQL 2005, I cannot create a foreign key constraint that references a column that does not have a unique constraint (primary key or otherwise).

我们在谈论什么数据库？在 SQL 2005 中，我无法创建引用没有唯一约束（主键或其他）的列的外键约束。

create table t1
(
  id int identity,
  fk int
);

create table t2
(
  id int identity,
);

CREATE NONCLUSTERED INDEX [IX_t2] ON [t2] 
(
    [id] ASC
);
ALTER TABLE t1 with NOCHECK
ADD CONSTRAINT FK_t2 FOREIGN KEY (fk)
    REFERENCES t2 (id) ;


Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 't2' 
that match the referencing column list in the foreign key 'FK_t2'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

If you could actually do this, you would effectively have a many-to-many relationship, which is not possible without an intermediate table. I would be truly interested in hearing more about this ...

如果您真的可以做到这一点，您将有效地拥有多对多关系，这在没有中间表的情况下是不可能的。我真的很想听到更多关于这个的信息......

See this related questionand answers as well.

另请参阅此相关问题和答案。