MySQL Foreign Key Tutorial


Posted by Andrew McCombe on Sat Jul 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.