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