MySQL – Why ALTER TABLE ADD FOREIGN KEY Creates Two Constraints

mariadbmysql-5.5

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?

Best Answer

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 constrain 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).

Related Question