I have a juction table that contains two foreign keys (from Profiles and Districts tables), with both columns as a composite primary key.
`profID` int(11) NOT NULL,
`distID` varchar(8) NOT NULL,
PRIMARY KEY (`profID`,`distID`)
I'd like to constrain both columns, but MySql throws an error:
#1050 - Table './database_name/z@002dprof@002ddist' already exists
In troubleshooting the problem, I've tried creating another duplicate junction table from scratch, but I get the same error. Oddly, MySQL will allow me to constrain one column or the other, but not both columns. I'm stumped, since I have other (non-junction) tables that have constraints on more than one foriegn key column.
By the way, I'm using phpMyAdmin, and all tables are InnoDB with utf-8.
Any help would be appreciated.
ADDED: SHOW CREATE TABLE results
CREATE TABLE `Profiles` (
`profID` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(64) NOT NULL,
`stID` varchar(2) NOT NULL,
`zip` varchar(5) NOT NULL,
PRIMARY KEY (`profID`),
KEY `stID` (`stID`,`zip`),
KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8
CREATE TABLE `Districts` (
`distID` varchar(8) NOT NULL,
`stID` varchar(2) NOT NULL,
`abbrev` varchar(16) NOT NULL,
PRIMARY KEY (`distID`),
KEY `stID` (`stID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `z-prof-dist` (
`profID` int(11) NOT NULL,
`distID` varchar(8) NOT NULL,
PRIMARY KEY (`profID`,`distID`),
KEY `distID` (`distID`),
KEY `profID` (`profID`),
CONSTRAINT `z-prof-dist_ibfk_1` FOREIGN KEY (`distID`) REFERENCES `Districts` (`distID`)
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I think I found a fix. Rather than using the phpMyAdmin function for adding a constraint (where I kept getting the error message), I instead followed marekful's lead by using an SQL ALTER TABLE query (with a new constraint name) as such:
ALTER TABLE `z-prof-dist`
ADD CONSTRAINT `test1`
FOREIGN KEY (`profID`) REFERENCES `Profiles` (`profID`)
ON UPDATE CASCADE
I still don't understand the cause of the original error, but I can see that the newly added foreign key constraint is working perfectly.