MySQL无法在引用1表的M：M表上创建外键 [英] MySQL Can't create Foreign key on a M:M table referencing 1 table

问题描述

我有一个客户表，其中客户将拥有零个或多个联系人，以及联系人可以在任何数量的客户身上。联系人位于客户表中。我创建了一个名为customer_contacts的表，其中包含2个PK，一个名为customer_PK，引用customer表的customer_PK，另一个名为contact_PK，引用customer表的customer_PK。我希望能够查找客户的所有联系人，或找到具有特定联系人的所有客户。我得到了MySQL建模者创建的FK创建错误。

  CREATE   TABLE   IF   NOT   EXISTS ` Abramsdb``customer_contact_test`（
`fustomer_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 无动作
  ON  更新无动作，
 < b>  CONSTRAINT `fk_contact` 
  FOREIGN   KEY （`contact_PK`）
  REFERENCES `Abramsdb``customer`（`customer_PK`）
  ON   DELETE 无动作
  ON   UPDATE  NO ACTION ）
 ENGINE = InnoDB 

错误我get是：错误代码：1215。无法添加外部约束。我删除了fk_contact约束并编译了代码。我在不同的表之间创建了这样的表，例如customer&电话处理M：M并没有问题。关于为什么和解决方案的任何想法？

解决方案

这部分不应该改变吗？



来自

  CONSTRAINT `fk_contact` 
  FOREIGN   KEY （`contact_PK`）
  REFERENCES `Abramsdb``customer`（` customer_PK`）

to

  CONSTRAINT  `fk_contact` 
  FOREIGN   KEY （`contact_PK`）
  REFERENCES `Abramsdb``contact`（`contact_PK`）

两个外键引用相同的表并且键。

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.

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?

解决方案

Shouldn't this part change?

From

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

to

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.

这篇关于MySQL无法在引用1表的M：M表上创建外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！