MySQL Foreign Key Tutorial

Image for MySQL Foreign Key Tutorial from unsplash.com

By Andrew McCombe
July 9, 2016


Here's a quick tutorial on creating foreign keys with MySQL.


DROP TABLE IF EXISTS `child`; DROP TABLE IF EXISTS `parent`; CREATE TABLE `parent` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned NOT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_parent_id` (`parent_id`), CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `parent` (`name`) VALUES ('Bob'), ('Alice'), ('Fred'); INSERT INTO `child` (`parent_id`,`name`) VALUES (1, 'Jack'), (2, 'John'), (3, 'Jill'), (3, 'Jane'), (3, 'James'); # delete a child - parent should not be removed DELETE FROM `child` WHERE `name` = 'Jane'; # Delete a parent - children should also be removed DELETE FROM `parent` WHERE `name` = 'James';

NOTES

A foreign column MUST have the same type as the foreign key. Null status and unsigned matter.