MySQL Foreign Key Tutorial

July 9th, 2016

Warning: This post is 8 years old. Some of this information may be out of date.

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.