MariaDB Foreign Key Constraint example

Foreign Key Constraints are used to model a parent/child relation in an entity relationship (ER) model:

SQL> CREATE TABLE team (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(48) NOT NULL
, PRIMARY KEY (id)
);

SQL> CREATE TABLE employee (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, first_name VARCHAR(32) NOT NULL
, last_name VARCHAR(64) NOT NULL
, team_id BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (id)
, CONSTRAINT `fk_employee_team` FOREIGN KEY (team_id)
  REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT
);
ERROR 1005 (HY000): Can't create table `test`.`employee` (errno: 150 "Foreign key constraint is incorrectly formed")

The same errors looks a bit different in MySQL 8.0:

ERROR 3780 (HY000): Referencing column 'team_id' and referenced column 'id' in foreign key constraint 'fk_team' are incompatible.

To see more details about this error you can look in the InnoDB status output:

SQL> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-08-19 09:45:39 0x7f06221be700 Error in foreign key constraint of table `test`.`employee`:
Create  table `test`.`employee` with foreign key `fk_team` constraint failed. Field type or character set for column 'team_id' does not mach referenced column 'id'.
...

In MySQL 8.0 there seems to be no more output for this error message in InnoDB status. :-(

After fixing the column type it works.

If you want to add a Foreign Key constraint to an existing table you can do it like this:

SQL> ALTER TABLE room
  ADD CONSTRAINT `fk_room_team` FOREIGN KEY (team_id)
  REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT;

If wee look at the table rooms we can see that the indexes are at least partially redundant:

SQL> CREATE TABLE `room` (
  `id` int(11) DEFAULT NULL,
  `room_number` varchar(10) DEFAULT NULL,
  `team_id` tinyint(3) unsigned DEFAULT NULL,
  UNIQUE KEY `team_id` (`team_id`,`room_number`),
  CONSTRAINT `fk_room_team` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

SQL> SELECT table_name, constraint_name, column_name, ordinal_position
  FROM information_schema.KEY_COLUMN_USAGE
 WHERE constraint_schema = 'test' AND table_name = 'room'
;
+------------+-----------------+-------------+------------------+
| table_name | constraint_name | column_name | ordinal_position |
+------------+-----------------+-------------+------------------+
| room       | team_id         | team_id     |                1 |
| room       | team_id         | room_number |                2 |
| room       | fk_room_team    | team_id     |                1 |
+------------+-----------------+-------------+------------------+

Dropping the Foreign Key Constraint again:

SQL> ALTER TABLE room DROP CONSTRAINT fk_room_team;

and creating it again with a hint to use the index:

SQL> ALTER TABLE room
  ADD CONSTRAINT `fk_room_team` FOREIGN KEY `team_id` (team_id)
  REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT;

does NOT end in the expected result. At least not if the index is a composite index. So we have to try with a non-composite index:

SQL> ALTER TABLE room
  ADD CONSTRAINT FOREIGN KEY (team_id)
  REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT;

SQL> CREATE TABLE `room` (
  `id` int(11) DEFAULT NULL,
  `room_number` varchar(10) DEFAULT NULL,
  `team_id` tinyint(3) unsigned DEFAULT NULL,
  KEY `team_id` (`team_id`),
  CONSTRAINT `room_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
;

SQL> ALTER TABLE room DROP CONSTRAINT room_ibfk_1, ADD CONSTRAINT `fk_room_team` FOREIGN KEY (team_id) REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT;

SQL> CREATE TABLE `room` (
  `id` int(11) DEFAULT NULL,
  `room_number` varchar(10) DEFAULT NULL,
  `team_id` tinyint(3) unsigned DEFAULT NULL,
  KEY `team_id` (`team_id`),
  CONSTRAINT `fk_room_team` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE,
  CONSTRAINT `room_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
;

This is not what we want:

+------------+-----------------+-------------+------------------+
| table_name | constraint_name | column_name | ordinal_position |
+------------+-----------------+-------------+------------------+
| room       | fk_room_team    | team_id     |                1 |
| room       | room_ibfk_1     | team_id     |                1 |
+------------+-----------------+-------------+------------------+

Fixing it:

SQL> ALTER TABLE room DROP CONSTRAINT room_ibfk_1, DROP CONSTRAINT fk_room_team;
SQL> ALTER TABLE room ADD CONSTRAINT `fk_room_team` FOREIGN KEY (team_id) REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT;

does work as expected.