MySQL的:使用两个外键到同一个表 [英] Mysql: using two foreign keys to the same table

问题描述

我正在使用MySQL工作台来设计数据库.服务器是mysql 5.5.6

I'm using MySQL workbench to design a database. Server is mysql 5.5.6

我已经定义了一些外键，这些外键将候选人"表链接到国家"表.我收到此错误:

I've defined a few foreign keys linking the "candidates" table to the "countries" table. I get this error:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'customer.candidats' (errno: 150)

问题是:我引用了countrys表两次:一次是国籍"列，一次是用户地址的原籍国.可以吗?这是正确的方法吗?

The thing is: i'm referencing twice the countries table: once for the "nationality" column, once for the user address's country of origin. Is that allowed? Is this the right way to do it?

这是似乎触发问题的生成代码.

Here is the generated code that seems to trigger the issue.

CREATE TABLE IF NOT EXISTS `customer`.`candidats` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(40) NULL,
  `prenom` VARCHAR(40) NULL,
  `qualite` ENUM('0001','0002') NULL COMMENT '0001 = Madame\n0002 = Monsieur',
  `sexe` SET('1','2') NULL COMMENT '1 = Femme\n2 = Homme',
  `date_de_naissance` DATE NULL,
  `Nationalite` INT NOT NULL,
  `selor_bilinguisme` TINYINT(1) NULL,
  `rue` VARCHAR(60) NULL,
  `numero` VARCHAR(10) NULL,
  `pays` INT NOT NULL,
  `region` INT NOT NULL,
  `localité` VARCHAR(40) NULL,
  `code_postal` VARCHAR(10) NULL,
  `email` VARCHAR(241) NULL,
  `tel_domicile` VARCHAR(30) NULL,
  `tel_bureau` VARCHAR(30) NULL,
  `tel_mobile` VARCHAR(30) NULL,
  `tel_prefere` ENUM('01','02','03') NULL DEFAULT '03',
  PRIMARY KEY (`id`),
  INDEX `fk_candidats_pays_idx` (`Nationalite` ASC, `pays` ASC),
  INDEX `fk_candidats_régions1_idx` (`region` ASC),
  CONSTRAINT `fk_candidats_pays`
    FOREIGN KEY (`Nationalite` , `pays`)
    REFERENCES `customer`.`pays` (`id` , `id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_candidats_régions1`
    FOREIGN KEY (`region`)
    REFERENCES `customer`.`régions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

付款"表(法语为国家")

The "pays" table ("countries" in French)

CREATE TABLE IF NOT EXISTS `customer`.`pays` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nom_fr` VARCHAR(45) NULL,
  `nom_nl` VARCHAR(45) NULL,
  `nationalite_fr` VARCHAR(45) NULL,
  `nationalite_nl` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB

推荐答案

您的模式生成器无法正常工作.

Your schema generator doesn't work correctly.

它应该生成:

CONSTRAINT `fk_candidats_pays`
    FOREIGN KEY (`pays`)
    REFERENCES `customer`.`pays` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `fk_candidats_Nationalite`
    FOREIGN KEY (`Nationalite`)
    REFERENCES `customer`.`pays` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

另一个问题:第一次看到这种类型的引用似乎很奇怪，但这很正常，我认为没有其他方法可以构建这种类型的关系.

To your other question: This type of referencing seems strange when you see it the first time, but it's quite normal and I think there is no other way of constructing this type of relationship.

这篇关于MySQL的:使用两个外键到同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！