MySQL Bugs: #1045: "Foreign key" clause doesn't report its name

Bug #1045	"Foreign key" clause doesn't report its name
Submitted:	13 Aug 2003 17:35	Modified:	20 Oct 2003 4:46
Reporter:	Matthias Urlichs	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.0 +BK	OS:	Linux (Linux 2.4.21)
Assigned to:		CPU Architecture:	Any

Description:
A named "foreign key" clause is reported without its name in mysqldump.

Deleting that foreign key thus is somewhat difficult. :-/

How to repeat:
mysql> create table b(i int primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> create table bb(i int primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> alter table b add a1 int, add index ib1(a1), add constraint cb1 foreign key(a1) references bb(i);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table b;

CREATE TABLE b (
  i int(11) NOT NULL default '0',
  a1 int(11) default NULL,
  PRIMARY KEY  (i),
  KEY ib1 (a1),
  FOREIGN KEY (`a1`) REFERENCES `bb` (`i`)
) TYPE=InnoDB CHARSET=latin1;

Suggested fix:
Please also drop the backquotes in the reported Foreign Key clause, while you're at it, if they haven't been requested; they're nonportable.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

From InnoDB manual:

"Starting from version 4.0.13, InnoDB supports

ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id

You have to use SHOW CREATE TABLE to find the internally generated foreign key id when you want to drop a foreign key."

Internal foreign key id is included in SHOW CREATE TABLE output

I've observed this same problem on MySQL 3.23.55. It's not a documentation problem - SHOW 
CREATE TABLE just doesn't list IDs. What is more, it allows multiple identical foreign keys to be 
created, which doesn't make any sense. There doesn't seem to be any way of deleting foreign keys 
at all - even "ALTER TABLE tablename DROP FOREIGN KEY" doesn't work (even though it thinks 
about it for a while!)!
Can you post the output from a version of MySQL that this does work properly in, using the above 
example?
If this is fixed in later versions, then this bug should be marked as "closed", not "not a bug".

Mathias,

the feature is only available in versions >= 4.0.13 and >= 4.1.1.

4.1.0 was released before 4.0.13.

Regards,

Heikki