MySQL Foreign Key Tutorial

July 9, 2016

Image for MySQL Foreign Key Tutorial from unsplash.com

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.