jeudi 22 janvier 2015

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


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?





Aucun commentaire:

Enregistrer un commentaire