MySQL:FKs到非唯一列

Question

我惊讶地发现MySQL允许FK使用非唯一列。我不确定这是否也适用于其他数据库，并且一直认为FK必须是唯一的-否则，我们如何知道子数据库的父行-但事实并非如此。这里有一把小提琴来说明这一点。我们首先创建3个表：

CREATE TABLE competitions(
cID INT UNSIGNED AUTO_INCREMENT,
title text not null,
primary key (cid)
) engine=innodb CHARACTER SET utf8mb4;

create table teams (
tID INT UNSIGNED AUTO_INCREMENT,
cid int unsigned not null,
name varchar(24) not null,
primary key (tid),
foreign key (cid) references competitions(cid)
) engine=innodb CHARACTER SET utf8mb4;

create table users (
row_id int unsigned auto_increment,
uID INT UNSIGNED not null,
tid int unsigned not null,
cid int unsigned not null,
primary key (row_id),
unique key (cid, uid),
foreign key (tid) references teams(tid),
foreign key (cid) references teams(cid)  /* refers to non-unique column */
) engine=innodb CHARACTER SET utf8mb4;

然后我们可以运行以下INSERT命令：

insert into competitions (title) values ('olympics 2020'), ('wimbledon 2021'), ('
ICC world cup 2022');
/* insert duplicate values in cid column.  */
insert into teams(cid, name) values (1, 'usa'), (1, 'china'), (2, 'germany'), (2, 'france'), (3, 'india'), (3, 'england');
/* the cid is a FK and is not unique in the parent table but MySQL does not complain! */
insert into users (cid, tid, uid) values (1, 1, 1);

我的问题是谁是(1,1,1)的父行？teams表中有两行具有cid=1。

Answer 1

这是InnoDB实现的一个特点。外键列必须引用任何索引的最左边的列。正如您所发现的，您可以使它引用一个非唯一索引。

您还可以使它引用唯一索引中最左边的列子集：

create table parent (id1 int, id2 int, primary key (id1, id2));
create table child (id1 int, foreign key (id1) references parent(id1) on delete cascade);

但这是非标准的，并且与其他SQL数据库不兼容。它提出了令人不安的问题：

mysql> insert into parent values (1,1), (1,2);
mysql> insert into child values (1);
mysql> delete from parent where id1=1 and id2=1;
mysql> select * from child;
Empty set (0.00 sec)

看起来，如果外键引用的任何行都被删除，那么这将导致删除级联。这就是我们想要的吗？即使父级中仍然存在满足外键引用的行？

mysql> select * from parent;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 |   2 |
+-----+-----+

尽管InnoDB允许，但我强烈建议您不要将表设计为依赖它。继续使外键只引用主键或唯一键，并且只引用这些键的完整列。

Answer 2

外键关系不是定义一个"父"；关系这只是说键值的组合存在于另一个表中。

在实践和SQL的定义中，引用的值应该是唯一的(最好是主键(。这在几乎所有数据库中都是必需的。

MySQL扩展了这个定义，允许任何索引列。