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

[10 Feb 2005 9:46] Dmitriy Kruglyak
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 !!!
[12 Feb 2005 13:35] Heikki Tuuri
Hi!

This is a feature request, not a bug.

This syntax is in the TODO:

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

Regards,

Heikki
[30 Sep 2008 10:31] Konstantin Osipov
WL#148
[9 Jan 2014 12:50] Erlend Dahl
Setting to 'verified' since we are discontinuing the use of 'to be fixed later'.