MySQL Bugs: #8415: ALTER TABLE syntax limitation with foreign key constraints

Bug #8415	ALTER TABLE syntax limitation with foreign key constraints
Submitted:	10 Feb 2005 9:46	Modified:	9 Jan 2014 12:50
Reporter:	Dmitriy Kruglyak	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1.9	OS:	Any (All)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This is a major problem for us, since our schema design tool only generates schema in the format that does not work. The problem does not come up with MyISAM.

How to repeat:
Create a new DB and then prepare two tables, without a problem:

====================
CREATE TABLE file_def (
       file_def_key         numeric(18) NOT NULL,
       file_name            VARCHAR(50)
);

ALTER TABLE file_def
       ADD PRIMARY KEY (file_def_key);

CREATE TABLE file_field_def (
       file_field_def_key   CHAR(18) NOT NULL,
       file_def_key         numeric(18),
       field_name           VARCHAR(50)
);

CREATE INDEX XIF1file_field_def ON file_field_def
(
       file_def_key
);

ALTER TABLE file_field_def
       ADD PRIMARY KEY (file_field_def_key);

====================

Now, this attempt to add a foreign key constraint fails with ERROR 1005 and errno 150:

ALTER TABLE file_field_def
       ADD FOREIGN KEY R_81 (file_def_key)
                             REFERENCES file_def
                             ON DELETE SET NULL
                             ON UPDATE SET NULL;

====================

However, this syntax succeeds:

ALTER TABLE file_field_def
       ADD CONSTRAINT R_81 FOREIGN KEY (file_def_key)
                             REFERENCES file_def(file_def_key)
                             ON DELETE SET NULL
                             ON UPDATE SET NULL;

Suggested fix:
Manual re-write of SQL is possible, but impractical since we have to use schema generation tool very often. Please fix upcoming 4.1 to support both types of SQL syntax !!!

Hi!

This is a feature request, not a bug.

This syntax is in the TODO:

http://www.innodb.com/todo.php

Regards,

Heikki

WL#148

Setting to 'verified' since we are discontinuing the use of 'to be fixed later'.