(错误)InnoDB MySQL错误1025，错误码150外键 [英] (Bug?) InnoDB MySQL error 1025, errno 150 Foreign Key

问题描述

我有一个要更改其主键的表. 这是表的定义.

I have a table whose primary key I'm trying to change. this is the table definition.

CREATE TABLE `tbl_customer` (
  `PersonId` int(11) NOT NULL,
  `Id` int(10) unsigned NOT NULL,
  `Name` varchar(100) collate utf8_spanish_ci NOT NULL,
  `Alias` varchar(50) collate utf8_spanish_ci NOT NULL,
  `Phone` varchar(30) collate utf8_spanish_ci default NULL,
  `Phone2` varchar(30) collate utf8_spanish_ci default NULL,
  `Email` varchar(50) collate utf8_spanish_ci default NULL,
  `Email2` varchar(50) collate utf8_spanish_ci default NULL,
  `RFC` varchar(13) collate utf8_spanish_ci default NULL,
  `AddressStreetName` varchar(45) collate utf8_spanish_ci default NULL,
  `AddressStreetNumber` varchar(45) collate utf8_spanish_ci default NULL,
  `AddressCityWard` varchar(45) collate utf8_spanish_ci default NULL,
  `AddressCityName` varchar(45) collate utf8_spanish_ci default NULL,
  `AddressStateName` varchar(45) collate utf8_spanish_ci default NULL,
  `AddressCountryName` varchar(45) collate utf8_spanish_ci default NULL,
  `AddressPostalCode` int(10) default NULL,
  `IsDistributor` tinyint(1) NOT NULL default '0' COMMENT '1 = Is Distributor, 0 = Is Not Distributor',
  `ParentCustomerId` int(10) NOT NULL default '11' COMMENT 'Our Id is 11, so by default, all customers right now are our children.',
  PRIMARY KEY  (`Id`),
  KEY `fk_tbl_cliente_tbl_cliente1_idx` (`ParentCustomerId`),
  KEY `fk_tbl_cliente_tbl_person1_idx` (`PersonId`),
  KEY `PersonId` (`PersonId`),
  KEY `PersonId_2` (`PersonId`),
  CONSTRAINT `fk_tbl_cliente_tbl_cliente1` FOREIGN KEY (`ParentCustomerId`) REFERENCES `tbl_customer` (`PersonId`),
  CONSTRAINT `fk_tbl_cliente_tbl_person1` FOREIGN KEY (`PersonId`) REFERENCES `zapata`.`tbl_person` (`Id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='''Customer'' refers to a person or entity to which we provide '$$

现在，当我第一次尝试:

Now, when I first tried to:

ALTER TABLE `tbl_customer` DROP PRIMARY KEY;

我的PRIMARY KEY是Id.当我尝试放下它时，我得到了..

My PRIMARY KEY is Id . When I tried to drop it I got..

Error Code: 1025. Error on rename of './services/#sql-29a_218cc7f' to './services/tbl_customer' (errno: 150)

因此，我删除了所有引用此表和列的FOREIGN KEY约束，但仍然遇到相同的错误.我还去了SHOW ENGINE INNODB STATUS并发现了这一点:

So, I deleted all FOREIGN KEY constraints that referred to this table and column, and still got the same error. I also went over to SHOW ENGINE INNODB STATUS And found out this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130226 14:41:11 Error in foreign key constraint of table services/tbl_employee_shift:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match to the ones in table. Constraint:
,
  CONSTRAINT fk_tbl_employee_shift_tbl_customer1 FOREIGN KEY (CustomerId) REFERENCES services.tbl_customer (Id) ON UPDATE CASCADE

但是，表services.tbl_employee_shift不存在(它曾经存在一次，但是在我尝试进行此更改之前几周已被删除).所以我继续...

However, the table services.tbl_employee_shift does not exist (it existed once but it was dropped several weeks before I tried this change). So I went on and...

CREATE TABLE services.tbl_employee_shift(
    CustomerId INT (11)
);
ALTER TABLE services.tbl_employee_shift ADD CONSTRAINT fk_tbl_employee_shift_tbl_customer1 FOREIGN KEY (CustomerId) REFERENCES avatar.tbl_cliente (Id);
ALTER TABLE services.tbl_employee_shift DROP FOREIGN KEY fk_tbl_employee_shift_tbl_customer1;

它可以工作...但是它不能纠正必要的信息，看来InnoDB仍然认为约束fk_tbl_employee_shift_tbl_customer1是有效的，因此在防止删除主键以保持一致性" ... 我正在使用MySQL 5.0.95.

And it works... but it doesn't correct the necessary information, seemingly InnoDB still believes that the constraint fk_tbl_employee_shift_tbl_customer1 is alive and thus, is 'preventing the drop of the primary key to keep consistency'... I'm using MySQL 5.0.95.

此问题未解决，已解决

仅当我们将数据库迁移到较新的服务器(相同的mysql版本)时，该问题才能得到纠正，似乎是对虚假外键(fk_tbl_employee_shift_tbl_customer1)的引用破了/鬼了，从而阻止了该列的删除.由于此损坏/ghostfk不在新服务器中，因此我可以毫无问题地删除该列.我的猜测是它是一个错误，但不幸的是我无法重新创建它.

The problem could only be corrected when we migrated the database to a newer server (same mysql version), seems like there was a broken/ghost reference to a ghost foreign key (fk_tbl_employee_shift_tbl_customer1 ) which prevented the column from being dropped. Since this broken/ghostfk wasn't in the new server, I could drop the column with no problems then. My guess is it was a bug, but unfortunately I can't recreate it.

推荐答案

听起来好像您在

It sounds as though you dropped tbl_employee_shift whilst foreign_key_checks was set to 0:

将 foreign_key_checks 设置为0也会影响数据定义语句: DROP SCHEMA 会删除模式，即使它包含表具有由架构外的表引用的外键以及 DROP TABLE 删除具有其他表引用的外键的表.

Setting foreign_key_checks to 0 also affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables.

由于已记录了此行为，因此必须将其视为设计原因，因此不应视为错误.

Since this behaviour is documented, it must be considered by-design and therefore not a bug.

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