How to lose your (foreign) key?

One of the many reasons to switch from MyISAM to InnoDB (or NDB) is the support of foreign keys which helps to guarantee referential integrity.

This is how it's supposed to work:

mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL, 
    -> pname VARCHAR(100) NOT NULL, PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child (id INT AUTO_INCREMENT NOT NULL, cname VARCHAR(100) NOT NULL, 
    -> pid INT NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY `fk_parent_child` (`id`) REFERENCES parent (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(100) NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

But unfortunately it sometimes doesn't work as we expect:

mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL, 
    -> pname VARCHAR(100) NOT NULL, PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child (id INT AUTO_INCREMENT NOT NULL, cname VARCHAR(100) NOT NULL, 
    -> pid INT NOT NULL REFERENCES parent(`id`), PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(100) NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is a known bug: Bug #17943.

But there is yet another way of losing a foreign key:

mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL, 
    -> pname VARCHAR(100) NOT NULL, PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child (id INT AUTO_INCREMENT NOT NULL, cname VARCHAR(100) NOT NULL COMMENT 'Parent\'s name', 
    -> pid INT NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY `fk_parent_child` (`id`) REFERENCES parent (`id`));
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE child\G 

*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(100) NOT NULL COMMENT 'Parent''s name',
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is also a known bug: Bug #68367

And the third way to lose a foreign key:

mysql> CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL, 
    -> pname VARCHAR(100) NOT NULL, PRIMARY KEY(`id`));                                                    
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE child (id INT AUTO_INCREMENT NOT NULL, cname VARCHAR(100) NOT NULL, 
    -> pid INT NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY `fk_parent_child` (`id`) REFERENCES parent (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE child\G 

*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(100) NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE child ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(100) NOT NULL,
  `pid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

This is not a bug, but it sometimes happens (especially with 5.1 and earlier). The developer creates a table and the realized he/she didn't specify the correct storage engine. This is well documented and well known.

So be careful and check if the key is where you expect it to be. With information_schema the information about foreign keys is not only available with SHOW statements but also as a table.

mysql> SELECT * FROM information_schema.referential_constraints WHERE CONSTRAINT_SCHEMA='fktest'\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: fktest
          CONSTRAINT_NAME: child_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: fktest
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: RESTRICT
              DELETE_RULE: RESTRICT
               TABLE_NAME: child
    REFERENCED_TABLE_NAME: parent
1 row in set (0.00 sec)