Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have a customer table where a customer will have zero or many contacts, and where a contact can be on any number of customers. The contacts are in the customer table. I created a table called customer_contacts with 2 PKs, one called customer_PK that references the customer_PK of the customer table, and the second PK called contact_PK that references the customer_PK of the customer table. I want to be able to look up all the contacts for a customer, or find all the customers with a specific contact. I get an error with the FK creation that was created by MySQL modeler.

SQL
CREATE TABLE IF NOT EXISTS `Abramsdb`.`customer_contact_test` (
  `customer_PK` INT NOT NULL,
  `contact_PK` INT NOT NULL,
  PRIMARY KEY (`customer_PK`, `contact_PK`),
  CONSTRAINT `fk_customer`
    FOREIGN KEY (`customer_PK`)
    REFERENCES `Abramsdb`.`customer` (`customer_PK`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contact`
    FOREIGN KEY (`contact_PK`)
    REFERENCES `Abramsdb`.`customer` (`customer_PK`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB


The error I get is: Error Code: 1215. Cannot add foreign constraint. I remove the fk_contact constraint and the code compiles. I have created tables like this between different tables like customer & phone to handle M:M and there is not an issue. Any ideas about why and a solution?
Posted
Updated 1-Oct-14 9:41am
v2

1 solution

Shouldn't this part change?

From
SQL
CONSTRAINT `fk_contact`
    FOREIGN KEY (`contact_PK`)
    REFERENCES `Abramsdb`.`customer` (`customer_PK`)

to
SQL
CONSTRAINT `fk_contact`
    FOREIGN KEY (`contact_PK`)
    REFERENCES `Abramsdb`.`contact` (`contact_PK`)

Seems strange to have both foreign keys referencing the same table and key.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900