I used to think that the foreign key column has to be unique [ref] but that is not the case.
Consider the tables below where we have a parent -> child -> grandchild relationship. A competition has teams and a team has users in it:
mysql> CREATE TABLE competitions(
-> cID INT UNSIGNED AUTO_INCREMENT,
-> title text not null,
-> primary key (cid)
-> ) engine=innodb CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> 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;
Query OK, 0 rows affected (0.01 sec)
mysql> 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;
Query OK, 0 rows affected (0.01 sec)
Create a few competitions:
mysql> insert into competitions (title) values ('olympics 2020'), ('wimbledon 2021'), ('
ICC world cup 2022');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
Now create a few teams:
mysql> insert into teams(cid, name) values (1, 'usa'), (1, 'china'), (2, 'germany'), (2, 'france'), (3, 'india'), (3, 'england');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
Now insert a user into competition 1 and team 1:
mysql> insert into users (cid, tid, uid) values (1, 1, 1);
Query OK, 1 row affected (0.00 sec)
Now insert same user in another team:
mysql> insert into users (cid, tid, uid) values (1, 2, 1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'users.cid'
This is good and unique key (cid, uid) is ensuring a user can be part of one team only within a competition.
What happens if we try to do this:
mysql> insert into users (cid, tid, uid) values (2, 2, 1);
Query OK, 1 row affected (0.00 sec)
This is undesired and we should have gotten an error because team 2 is associated with competition 1. The cid is not an independent variable. Its value depends on tid. How can we accomplish this in MySQL?
mysql> select * from teams;
+-----+-----+---------+
| tID | cid | name |
+-----+-----+---------+
| 1 | 1 | usa |
| 2 | 1 | china |
| 3 | 2 | germany |
| 4 | 2 | france |
| 5 | 3 | india |
| 6 | 3 | england |
+-----+-----+---------+
6 rows in set (0.00 sec)
mysql> select * from users;
+--------+-----+-----+-----+
| row_id | uID | tid | cid |
+--------+-----+-----+-----+
| 1 | 1 | 1 | 1 |
| 3 | 1 | 2 | 2 |
+--------+-----+-----+-----+
2 rows in set (0.00 sec)
siddharth's space 