时间戳

首页 > 解决方案 > MySQL：对非唯一列的 FK

mysql - MySQL：对非唯一列的 FK

问题描述

正如我惊讶地发现的那样，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。

标签： mysqlforeign-keys

解决方案

This is a peculiarity of the implementation of InnoDB. The foreign key column(s) must reference the leftmost column(s) of any index. You can make it reference a non-unique index, as you discovered.

You can also make it reference a leftmost subset of columns in a unique index:

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

But this is nonstandard, and incompatible with other SQL databases. It brings up uncomfortable questions:

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)

It seems that if any row referenced by the foreign key is deleted, then this causes the delete to cascade. Is this what is desired? Even though there still exists a row in parent that satisfies the foreign key reference?

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

Even though it is allowed by InnoDB, I strongly recommend you don't design your tables to depend on it. Keep making foreign keys reference only primary keys or unique keys, and only the complete set of columns of those keys.

推荐阅读