Why does this ALTER TABLE ADD FOREIGN KEY query create two foreign key constraints

Question

I am attempting to add a foreign key constraint to existing tables via an ALTER TABLE statement:

CREATE TABLE `blog` (
 `blogid` int(11) NOT NULL AUTO_INCREMENT,
 `deptid` int(11) NOT NULL DEFAULT '-1',
 `name` varchar(255) NOT NULL DEFAULT '',
 PRIMARY KEY (`blogid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `dept` (
 `did` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(50) NOT NULL DEFAULT '',
 PRIMARY KEY (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE blog
ADD CONSTRAINT blog_deptid_ibfk_dept
FOREIGN KEY (deptid) REFERENCES dept (did);

The query runs without error but it creates two foreign key constraints and not just one:

CREATE TABLE `blog` (
 `blogid` int(11) NOT NULL AUTO_INCREMENT,
 `deptid` int(11) NOT NULL DEFAULT '-1',
 `name` varchar(255) NOT NULL DEFAULT '',
 PRIMARY KEY (`blogid`),
 KEY `blog_deptid_ibfk_dept` (`deptid`),
 CONSTRAINT `blog_deptid_ibfk_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`),
 CONSTRAINT `blog_ibfk_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm using MariaDB 5.5[.33a]. I have tested this on MySQL 5.5.32 using SQL Fiddle and the results are the same.

Interestingly, ALTER TABLE blog DROP FOREIGN KEY blog_ibfk_dept; results in both constraints being dropped, but ALTER TABLE blog DROP FOREIGN KEY blog_deptid_ibfk_dept; results in only that one constraint being dropped.

I cannot for the life of me figure out why two constraints are being created. Can someone explain on this unexpected behavior?

• JSmitty: It works as expected in MySQL 5.6.6 m9. This is starting to look like a bug in MySQL/MariaDB 5.5.
• eroomydna: What has information_schema got records for?
• JSmitty: information_schema shows records for both foreign keys. Based on the explanation provided in the accepted answer my workaround is to use only fk in the constraint name instead of ibfk as I am not able to update the DBMS on the servers.

Answer 1

This looks like a bug in the lexer/parser logic.

My theory is that it has to do with the name of the constraint. When a foreign key constraint is defined without a name, MySQL assigns automatically a name of its choice. This is usually of the form referencingtable_ibfk_X.

Since the bug appears when we define a name for the constraint that contains the string _ibfk_, we can conclude that the parser gets confused somewhere and adds the 2nd constraint (see sql-fiddle , bug appears in version 5.5.32).

And yes, it's a known bug . It seems that it was fixed in some older minor 5.5 versions but then reappeared. Please check the latest 5.5 and 5.6 versions (5.5.34 and 5.6.22) if it has been fixed (the bug does not appear in 5.6.6).

• JSmitty: Thank you for the explanation! The bug has been resolved by MariaDB 5.5.41, but I have not tested MySQL beyond what is available via SQL Fiddle.
• Josh M.: I tried to fix the typo in the 3rd paragraph (contrain -> contraints), but edits must be six characters at least. This is extremely dumb.
• ypercubeᵀᴹ: @JoshM. fixed :)