Search code examples
mysqlforeign-key-relationshipjunction-table

Can't Constrain Both Junction Table Columns


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

Solution

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