MySQL外键错误1005 errno 150主键作为外键 [英] MySQL Foreign Key Error 1005 errno 150 primary key as foreign key

问题描述

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

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).

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

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

显示引擎状态:

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

Error in foreign key constraint of table dbimmobili/valutazionimercato:

在被引用的表中找不到索引，在该索引中被引用的列显示为第一列，或者表和被引用的表中的列类型不符合约束条件.请注意，在使用> = InnoDB-4.1.12创建的表中，ENUM和SET的内部存储类型已更改，并且新表中的此类列不能引用旧表中的此类列.

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.

我在哪里做错了?

推荐答案

在创建外键约束时，MySQL既需要在引用表上也需要在引用表上使用可用索引.如果不存在引用表上的索引，则会自动创建索引，但是需要手动创建引用表上的索引(

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.

测试用例:

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)

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

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.

这篇关于MySQL外键错误1005 errno 150主键作为外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！