Bug #16614 SHOW CREATE TABLE does not show constraints for InnoDB tables
Submitted: 18 Jan 2006 19:15 Modified: 7 Jul 2006 0:32
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.1.5 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[18 Jan 2006 19:15] Mark Matthews
Description:
The output of "SHOW CREATE TABLE" does not show constraints added to InnoDB tables.

mysql> create table `app tab` ( C1 int(11) NULL, INDEX NEWINX (C1), INDEX NEWINX2 (C1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `app tab` ADD CONSTRAINT APPFK FOREIGN KEY (C1) REFERENCES `app tab` (C1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `app tab`;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| app tab | CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

This probably breaks "mysqldump" as well, since I tested it, and the foreign key constraints just aren't in the dump at all.

How to repeat:
mysql> create table `app tab` ( C1 int(11) NULL, INDEX NEWINX (C1), INDEX NEWINX2 (C1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `app tab` ADD CONSTRAINT APPFK FOREIGN KEY (C1) REFERENCES `app tab` (C1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `app tab`;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| app tab | CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>
[18 Jan 2006 20:30] MySQL Verification Team
mysql> show create table `app tab`\G
*************************** 1. row ***************************
       Table: app tab
Create Table: CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`),
  CONSTRAINT `APPFK` FOREIGN KEY (`C1`) REFERENCES `app tab` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.19-debug |
+--------------+
1 row in set (0.00 sec)

mysql> show create table `app tab`\G
*************************** 1. row ***************************
       Table: app tab
Create Table: CREATE TABLE `app tab` (
  `C1` int(11) default NULL,
  KEY `NEWINX` (`C1`),
  KEY `NEWINX2` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.6-alpha-debug |
+-------------------+
1 row in set (0.01 sec)
[2 Jun 2006 12:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7206
[2 Jun 2006 13:12] Alexander Barkov
Test results display encoded table name in REFERENCES:

+app tab	CREATE TABLE `app tab` (
+  `C1` int(11) DEFAULT NULL,
+  KEY `NEWINX` (`C1`),
+  KEY `NEWINX2` (`C1`),
+  CONSTRAINT `APPFK` FOREIGN KEY (`C1`) REFERENCES `app@0020tab` (`C1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table `app tab`;

It should be `app tab` instead of `app@0020tab`

Setting back to "in progress".
[7 Jul 2006 0:32] Paul DuBois
Noted in 5.1.12 changelog.

SHOW CREATE TABLE now shows constraints for InnoDB tables.