MySQL Bugs: #3332: Error on foreign key creation.

Bug #3332	Error on foreign key creation.
Submitted:	30 Mar 2004 4:19	Modified:	30 Mar 2004 6:28
Reporter:	SINISA MILIVOJEVIC	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Any (any)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
If a script below is run, the last line create then error
Can't create table '.\prova\#sql-654_2e.frm' (errno: 121)

If you run the code on 4.0.17 all it is ok.

How to repeat:
CREATE TABLE RevisioniSL(SchedaLavoro CHAR(8) NOT NULL,NRev SMALLINT NOT
NULL,DataRevisione DATETIME NULL,DescrizioneRev CHAR(255) NULL,IdaReg
DATETIME NULL,IdaVar DATETIME NULL,UserReg CHAR(30) NULL,UserVar CHAR(30)
NULL,WStatReg CHAR(30) NULL,WStatVar CHAR(30) NULL, PRIMARY KEY
(SchedaLavoro, NRev))TYPE=InnoDB;
ALTER TABLE RevisioniSL ADD LIFEBELT TIMESTAMP;
CREATE TABLE schedelavoro(SchedaLavoro CHAR(8) NOT NULL,Articolo CHAR(19)
NULL,Operazione CHAR(4) NULL,DataRilascio DATETIME NULL,Lotto CHAR(10)
NULL,DataConsOfficina DATETIME NULL,DataAccettazOfficina DATETIME
NULL,DataRitiro DATETIME NULL,NoteRitiro TEXT NULL,IDUSL INT
NULL,IdaUltAggAS DATETIME NULL,AS400 SMALLINT NULL,IdaReg DATETIME
NULL,IdaVar DATETIME NULL,UserReg CHAR(30) NULL,UserVar CHAR(30)
NULL,WStatReg CHAR(30) NULL,WStatVar CHAR(30) NULL, PRIMARY KEY
(SchedaLavoro))TYPE=InnoDB;
ALTER TABLE schedelavoro ADD LIFEBELT TIMESTAMP;
CREATE TABLE TabUbicazioneSL(IDUSL INT NOT NULL AUTO_INCREMENT,UbicazioneSL
CHAR(20) NULL,IdaReg DATETIME NULL,IdaVar DATETIME NULL,UserReg CHAR(30)
NULL,UserVar CHAR(30) NULL,WStatReg CHAR(30) NULL,WStatVar CHAR(30) NULL,
PRIMARY KEY (IDUSL))TYPE=InnoDB;
ALTER TABLE TabUbicazioneSL ADD LIFEBELT TIMESTAMP;
CREATE INDEX ArtOper ON schedelavoro (Articolo, Operazione);
CREATE INDEX IDUSL ON schedelavoro (IDUSL);
CREATE UNIQUE INDEX UbicazioneSL ON TabUbicazioneSL (UbicazioneSL);
CREATE INDEX SchedaLavoro ON RevisioniSL (SchedaLavoro);
ALTER TABLE RevisioniSL ADD CONSTRAINT FOREIGN KEY (SchedaLavoro) REFERENCES
schedelavoro (SchedaLavoro) ON DELETE CASCADE;
ALTER TABLE schedelavoro ADD CONSTRAINT FOREIGN KEY (IDUSL) REFERENCES
TabUbicazioneSL (IDUSL);

Hi!

------------------------
LATEST FOREIGN KEY ERROR
------------------------
040330 16:05:21 Error in foreign key constraint creation for table test/#sql-e38
_1.
A foreign key constraint of name test/FOREIGN
already exists (note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).

InnoDB seems to take the word 'FOREIGN' as the constraint id.

Workaround: do not specify put the word CONSTRAINT to the clauses if you do not explicitly specify the constraint name.

Marko will fix this bug to 4.0.19.

Thank you,

Heikki

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The foreign key constraint parser in InnoDB expected an identifier after the keyword CONSTRAINT, like this: CONSTRAINT constraint_id FOREIGN KEY .... I have now fixed it (ChangeSet 1.1754), so that CONSTRAINT FOREIGN KEY ... works.