Foreign key constraints missing from SHOW CREATE TABLE output

[tom worster]

I'm finding that SHOW CREATE TABLE is not showing foreign key constraints as I would expect.

To demonstrate, here’s an example from the MySQL manual:

CREATE TABLE parent (
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
  id INT, parent_id INT,
  INDEX par_ind (parent_id),
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;

mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE "child" (
  "id" int(11) default NULL,
  "parent_id" int(11) default NULL,
  KEY "par_ind" ("parent_id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

In that output I would have expected to see something like:

CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`) ON DELETE CASCADE

in that create table output but clearly it's not there.

The constraint does however appear to exist:

mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema=database()\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: NULL
            CONSTRAINT_SCHEMA: test_fk
              CONSTRAINT_NAME: child_ibfk_1
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: test_fk
                   TABLE_NAME: child
                  COLUMN_NAME: parent_id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test_fk
        REFERENCED_TABLE_NAME: parent
       REFERENCED_COLUMN_NAME: id
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: NULL
            CONSTRAINT_SCHEMA: test_fk
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: test_fk
                   TABLE_NAME: parent
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
2 rows in set (0.01 sec)

mysql> SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_schema=database()\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: NULL
 CONSTRAINT_SCHEMA: test_fk
   CONSTRAINT_NAME: child_ibfk_1
      TABLE_SCHEMA: test_fk
        TABLE_NAME: child
   CONSTRAINT_TYPE: FOREIGN KEY
*************************** 2. row ***************************
CONSTRAINT_CATALOG: NULL
 CONSTRAINT_SCHEMA: test_fk
   CONSTRAINT_NAME: PRIMARY
      TABLE_SCHEMA: test_fk
        TABLE_NAME: parent
   CONSTRAINT_TYPE: PRIMARY KEY
2 rows in set (0.01 sec)

I get the same results with examples of my own design.

Any idea what's going on?