MySQL 外键错误 1005 errno 150 主键作为外键

我正在使用 MySQL Workbench 制作一个小型数据库.我有一个名为Immobili"的主表，它有一个由四列组成的主键:(Comune、Via、Civico、Immobile).

我还有另外三个表，它们具有相同的主键(Comune、Via、Civico、Immobile)，但这些字段也引用了表 Immobili.

第一个问题:我可以制作一个同时也是外键的主键吗?

第二个问题:当我尝试导出更改时，它说:

在服务器中执行SQL脚本# 错误:错误 1005:无法创建表 'dbimmobili.condoni' (errno: 150)如果不存在，则创建表`dbimmobili`.`Condoni`(`ComuneImmobile` VARCHAR(50) NOT NULL ,`ViaImmobile` VARCHAR(50) NOT NULL ,`CivicoImmobile` VARCHAR(5) NOT NULL ,`InternoImmobile` VARCHAR(3) NOT NULL ,`ProtocolloNumero` VARCHAR(15) NULL ,`DataRichiestaSanatoria` DATE NULL ,`DataSanatoria` DATE NULL ,`SullePartiEsclusive` TINYINT(1) NULL ,`SullePartiComuni` TINYINT(1) NULL ,`OblazioneInEuro` DOUBLE NULL ,`TecnicoOblazione` VARCHAR(45) NULL ,`TelefonoTecnico` VARCHAR(15) NULL ,索引`ComuneImmobile`(`ComuneImmobile` ASC)，索引`ViaImmobile`(`ViaImmobile` ASC)，索引`CivicoImmobile`(`CivicoImmobile`ASC)，索引`InternoImmobile`(`InternoImmobile` ASC)，PRIMARY KEY (`ComuneImmobile`、`ViaImmobile`、`CivicoImmobile`、`InternoImmobile`) ,约束`ComuneImmobile`外键(`ComuneImmobile`)参考文献`dbimmobili`.`Immobile`(`ComuneImmobile`)删除级联在更新级联，约束`ViaImmobile`外键(`ViaImmobile`)参考文献`dbimmobili`.`Immobile`(`ViaImmobile`)删除级联在更新级联，约束`CivicoImmobile`外键(`CivicoImmobile`)参考文献`dbimmobili`.`Immobile`(`CivicoImmobile`)删除级联在更新级联，约束`InternoImmobile`外键(`InternoImmobile`)参考文献`dbimmobili`.`Immobile`(`InternoImmobile`)删除级联更新级联) 引擎 = InnoDB

显示引擎状态:

<块引用>

表 dbimmobili/valutazionimercato 的外键约束错误:

<块引用>

在被引用的表中找不到索引，其中被引用的列显示为第一列，或者表中的列类型与被引用表的约束不匹配.注意，在>= InnoDB-4.1.12创建的表中，ENUM和SET的内部存储类型发生了变化，旧表中的此类列不能被新表中的此类列引用.

我哪里做错了?

解决方案

在创建外键约束时，MySQL 需要在引用表和被引用表上都有一个可用的索引.如果引用表上的索引不存在，则自动创建，但需要手动创建引用表上的索引(来源).你的似乎不见了.

测试用例:

创建表 tbl_a (id int 主键，some_other_id int,值整数) 引擎=INNODB;查询正常，0 行受影响(0.10 秒)创建表 tbl_b (id int 主键，a_id 整数，外键 (a_id) 参考 tbl_a (some_other_id)) 引擎=INNODB;错误 1005 (HY000): 无法创建表 'e.tbl_b' (errno: 150)

但是如果我们在 some_other_id 上添加一个索引:

CREATE INDEX ix_some_id ON tbl_a (some_other_id);查询正常，0 行受影响(0.11 秒)记录:0 重复:0 警告:0创建表 tbl_b (id int 主键，a_id 整数，外键 (a_id) 参考 tbl_a (some_other_id)) 引擎=INNODB;查询正常，0 行受影响(0.06 秒)

这在大多数情况下通常不是问题，因为被引用的字段通常是被引用表的主键，并且主键会自动建立索引.

I'm making a small DataBase with MySQL Workbench. I have a main table, called "Immobili", which has a Primary Key composed by four columns: (Comune, Via, Civico, Immobile).

I also have three other tables, which have the same primary key (Comune, Via, Civico, Immobile), but these fields are also referenced to the table Immobili.

First question: Can I make a Primary Key that is also a Foreign Key?

Second Question: When I try to export the changes it says:

Executing SQL script in server

# ERROR: Error 1005: Can't create table 'dbimmobili.condoni' (errno: 150)

CREATE  TABLE IF NOT EXISTS `dbimmobili`.`Condoni` (

  `ComuneImmobile` VARCHAR(50) NOT NULL ,
  `ViaImmobile` VARCHAR(50) NOT NULL ,
  `CivicoImmobile` VARCHAR(5) NOT NULL ,
  `InternoImmobile` VARCHAR(3) NOT NULL ,
  `ProtocolloNumero` VARCHAR(15) NULL ,
  `DataRichiestaSanatoria` DATE NULL ,
  `DataSanatoria` DATE NULL ,
  `SullePartiEsclusive` TINYINT(1) NULL ,
  `SullePartiComuni` TINYINT(1) NULL ,
  `OblazioneInEuro` DOUBLE NULL ,
  `TecnicoOblazione` VARCHAR(45) NULL ,
  `TelefonoTecnico` VARCHAR(15) NULL ,
  INDEX `ComuneImmobile` (`ComuneImmobile` ASC) ,
  INDEX `ViaImmobile` (`ViaImmobile` ASC) ,
  INDEX `CivicoImmobile` (`CivicoImmobile` ASC) ,
  INDEX `InternoImmobile` (`InternoImmobile` ASC) ,

  PRIMARY KEY (`ComuneImmobile`, `ViaImmobile`, `CivicoImmobile`, `InternoImmobile`) ,

  CONSTRAINT `ComuneImmobile`
    FOREIGN KEY (`ComuneImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ComuneImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `ViaImmobile`
    FOREIGN KEY (`ViaImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ViaImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `CivicoImmobile`
    FOREIGN KEY (`CivicoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`CivicoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `InternoImmobile`
    FOREIGN KEY (`InternoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`InternoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB

Showing the Engine Status:

Error in foreign key constraint of table dbimmobili/valutazionimercato:

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or columns typse in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

Where am I doing wrong?

解决方案

When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn't exist, but the one on the referenced table needs to be created manually (Source). Yours appears to be missing.

Test case:

CREATE TABLE tbl_a (
    id int PRIMARY KEY,
    some_other_id int,
    value int
) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
ERROR 1005 (HY000): Can't create table 'e.tbl_b' (errno: 150)

But if we add an index on some_other_id:

CREATE INDEX ix_some_id ON tbl_a (some_other_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

This is often not an issue in most situations, since the referenced field is often the primary key of the referenced table, and the primary key is indexed automatically.