Four foreign keys two per table cannot be referenced to a single primary key in main table

Question

Good day

I'm trying to solve an issue with foreign key relationship between three tables. Basically I have two secondary tables, each one has two foreign keys and each foreign key is referenced to a single primary key in the third table.

This same issue happens in a much larger database, however due to its complexity, size and possible copyright issues. I had no choice but to isolate the problem and create a replica that would result in the same issue. However if you must know, the same two tables that reference the main table People are just two in a universe of dozens of the same kind in the database in question.

My hope is that someone can point the problem and maybe provide a solution that wouldn't effect much of the current structure, so that I can apply it myself, probably to all other tables because from what I see of the database diagram, its likely they will all suffer from the same problem. Database authorship is not mine, which only adds to one's confusion when looking at the diagram.

The SQL create script:

CREATE SCHEMA IF NOT EXISTS `sampleBD` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `sampleBD` ;

-- -----------------------------------------------------
-- Table `sampleBD`.`People`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `sampleBD`.`People` (
  `PeopleID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(200) NOT NULL ,
  `EntryDate` DATETIME NULL ,
  `EntryBy` INT NULL ,
  PRIMARY KEY (`PeopleID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sampleBD`.`PeopleNumberId`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `sampleBD`.`PeopleNumberId` (
  `PeopleNumberIdID` INT NOT NULL AUTO_INCREMENT ,
  `PeopleID` INT NOT NULL ,
  `NumberId` INT(11) NOT NULL ,
  `EntryDate` DATETIME NULL ,
  `EntryBy` INT NULL ,
  PRIMARY KEY (`PeopleNumberIdID`) ,
  INDEX `PeopleID` (`PeopleID` ASC) ,
  INDEX `EntryBy` (`EntryBy` ASC) ,
  CONSTRAINT `PeopleID`
    FOREIGN KEY (`PeopleID` )
    REFERENCES `sampleBD`.`People` (`PeopleID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `EntryBy`
    FOREIGN KEY (`EntryBy` )
    REFERENCES `sampleBD`.`People` (`PeopleID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sampleBD`.`PeopleCbi`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `sampleBD`.`PeopleCbi` (
  `PeopleCbiID` INT NOT NULL AUTO_INCREMENT ,
  `PeopleID` INT NOT NULL ,
  `Cbi` INT NOT NULL ,
  `EntryDate` DATETIME NULL ,
  `EntryBy` INT NULL ,
  PRIMARY KEY (`PeopleCbiID`) ,
  INDEX `PessoaID` (`PeopleID` ASC) ,
  INDEX `EntryBy` (`EntryBy` ASC) ,
  CONSTRAINT `PessoaID`
    FOREIGN KEY (`PeopleID` )
    REFERENCES `sampleBD`.`People` (`PeopleID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `EntryBy`
    FOREIGN KEY (`EntryBy` )
    REFERENCES `sampleBD`.`People` (`PeopleID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The script is able to create the first and second tables, however when it reaches the third table it returns an error code.

Error Code: 1005. Can't create table 'samplebd.peoplecbi' (errno: 121)

It's a foreign key problem, but I'm not sure how it could be fixed without altering the current structure of the table or tables involved.

Thanks for the help.

@sarwar026: Thanks. It provided a new MySql command that will prove quite useful for future reference. — Fábio Antunes, Apr 13 '12 at 17:51

score 1 · Accepted Answer · answered Apr 13 '12 at 17:46

1

This looks like a name conflict between foreign key constraint names. Constraint names must be unique in a database, like table names are.

So just choose another name for the constraint EntryBy in the 3rd table. It will not affect any of the functionality you have.

answered Apr 13 '12 at 17:46

Dmytro Shevchenko

33,431
6
51
67

I was under the impression, that constraint names had to be unique in a table, not in the whole database. That explains it all now. Thanks. This workaround will work seamlessly. – Fábio Antunes Apr 13 '12 at 17:49

Four foreign keys two per table cannot be referenced to a single primary key in main table

1 Answers1