1

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?

  • It works as expected in MySQL 5.6.6 m9. This is starting to look like a bug in MySQL/MariaDB 5.5. – JSmitty Jan 22 '15 at 23:27
  • What has information_schema got records for? – eroomydna Jan 23 '15 at 0:23
  • 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. – JSmitty Jan 23 '15 at 18:55
0

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

  • 1
    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. – JSmitty Jan 23 '15 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.