CODEWITHSUNDEEP
mysqlforeign-keys
morpheus
morpheus

Reputation: 20320

MySQL: FKs to non-unique column

As I found out to my surprise MySQL allows FKs to non-unique columns. I am not sure if this applies to other databases as well and always thought the FK had to be unique - otherwise, how do we know the parent row of a child - but looks like that is not the case. Here is a fiddle that illustrates this. We first create 3 tables:

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;

Then we can run following INSERT Commands:

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

My question is who is the parent row of (1,1,1)? There are two rows in the teams table with cid=1.

Upvotes: 1

Views: 190

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

A foreign key relationship is not defining a "parent" relationship. It is simply saying that a combination of key values is present in another table.

In practice and in the definition of SQL, the referenced value should be unique (and preferably a primary key). This is required in almost all databases.

MySQL extends this definition to allow any indexed columns.

Upvotes: 1

Related Questions