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
!!!
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 !!!