MySQL Bugs: #60331: foreign keys don't show in "show create table".

Bug #60331	foreign keys don't show in "show create table".
Submitted:	4 Mar 2011 9:30	Modified:	17 Mar 2011 19:30
Reporter:	Uri Even-Chen	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.93, 5.1.41, 5.1.54	OS:	Any (Ubuntu 10.04 LTS, Windows, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	foreign, ignored, keys

Description:
I created a new database with foreign keys (using "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;"). After creating the database from a file ("mysql --user=root --password < Schema.sql"), I checked tables with "show create table". Most of the tables appear ok, with foreign keys showing as "CONSTRAINT" (up to 4 foreign keys per table). But with tables where I defined 5 foreign keys, I don't see any foreign keys at all in "show create table". I have a few tables with 5 foreign keys defined, and I want to know if the foreign keys are defined in the database. But when I check these tables with "show create table", I see keys (one "PRIMARY KEY" and 4 "KEY"s) but I don't see any "CONSTRAINT"s at all. 

Is there a limit on the number of foreign keys per table? And why didn't I receive any error message when creating the tables? 

I checked and even if I define only one foreign key for this table, the foreign key is not created! The foreign key is ignored, but a regular key is created.

By the way, I'm using "mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1" on Ubuntu 10.04 LTS.

Uri Even-Chen 
E-mail: uri@speedy.net 
Website: http://www.speedy.net/

How to repeat:
please see the attached file.

I've got the same result (no constraints created for 2 tables) with 5.1.54 on Windows.

I couldn't repeat on Windows with latest source, please try at least with server version: 5.1.56.

http://downloads.mysql.com/archives.php?p=mysql-5.1

Thanks in advance.

I have checked the problem here with 5.0.51a-3ubuntu5.8 and the problem seems to be a comment in the OP DDL.
I'll check with OP and if he allows me, I'll post it here.

When the comment for the field "password" is present, the constraint is not created. A key is created instead.

Attachment: comment_issue.sql (application/octet-stream, text), 625 bytes.

OK, I removed all field comments and now it works, all the foreign keys are defined in all tables.

I'm sorry but I can't upgrade MySQL, I'm using whatever comes with Ubuntu 10.04 LTS.

It seems that the backtic in the comment is the problem.
Removing just that one character allows the FOREIGN KEY to be defined.

Here is a simplified test case:

macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.93-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table master(c1 int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql> create table slave(c1 int primary key, c2 int comment 'comment', constraint fk1 foreign key(c2) references master(c1)) engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> show create table slave\G
*************************** 1. row ***************************
       Table: slave
Create Table: CREATE TABLE `slave` (
  `c1` int(11) NOT NULL,
  `c2` int(11) default NULL COMMENT 'comment',
  PRIMARY KEY  (`c1`),
  KEY `fk1` (`c2`),
  CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `master` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table slave;
Query OK, 0 rows affected (0.00 sec)

mysql> create table slave(c1 int primary key, c2 int comment 'com`ment', constraint fk1 foreign key(c2) references master(c1)) engine=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table slave\G
*************************** 1. row ***************************
       Table: slave
Create Table: CREATE TABLE `slave` (
  `c1` int(11) NOT NULL,
  `c2` int(11) default NULL COMMENT 'com`ment',
  PRIMARY KEY  (`c1`),
  KEY `fk1` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And, indeed, current mysql-5.1 from bzr is NOT affected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table master(c1 int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> create table slave(c1 int primary key, c2 int comment 'com`ment', constraint fk1 foreign key(c2) references master(c1)) engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table slave\G
*************************** 1. row ***************************
       Table: slave
Create Table: CREATE TABLE `slave` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL COMMENT 'com`ment',
  PRIMARY KEY (`c1`),
  KEY `fk1` (`c2`),
  CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `master` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I do not see any explanation on how this was fixed in changelogs of 5.1.55 or 5.1.56.