(错误?)InnoDB MySQL错误1025,errno 150外键

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;

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

我的主键是Id。当我想放弃的时候，我得到了…

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

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:

因此，我删除了所有引用此表和列的外键约束，仍然得到相同的错误。我也去展示了引擎INNODB的状态，并发现了这个:

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

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

然而,表服务。tbl_employee_shift不存在(它只存在一次，但在我尝试此更改之前，它被删除了几个星期)。所以我继续……

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;

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.

和它的工作原理……但它并没有纠正必要的信息，似乎InnoDB仍然认为约束fk_tbl_employee_shift_tbl_customer1是活的，因此，它是“阻止主键的下降以保持一致性”……我使用MySQL 5.0.95。

EDIT: This problem went unresolved, it was worked around

编辑:这个问题没有解决，它被解决了

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.

只有当我们将数据库迁移到一个更新的服务器(相同的mysql版本)时，问题才会得到纠正，似乎有一个针对ghost外键(fk_tbl_employee_shift_tbl_customer1)的断/鬼引用，阻止了删除列。由于这个坏掉的/ghostfk不在新服务器中，所以我可以删除这个列而不会有任何问题。我猜这是一个错误，但不幸的是我不能重新创建它。

4 个解决方案

#1

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

听起来好像你放弃了tbl_employee_shift，而foreign_key_check设置为0:

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.

将foreign_key_check设置为0也会影响数据定义语句:DROP SCHEMA会删除一个模式，即使它包含由模式外的表引用的外键的表，而DROP TABLE会删除包含其他表引用的外键的表。

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

由于这种行为是有记录的，所以它必须被认为是设计的，因此不是一个bug。

#2

The error occurs when foreign key is bad formulated. the SQL is correct, i run script in my localhost, i get same error. the solution is verify that table tbl_person was created with engine "InnoDB".

当外键的格式不正确时发生错误。SQL是正确的，我在本地主机上运行脚本，我得到了相同的错误。解决方案是验证表tbl_person是使用引擎“InnoDB”创建的。

greetings

问候

#3

Just dealt with this problem today. There was no sign of the two affected tables in any of the schema information tables. I searched the various system databases looking for the FK, to no avail. In the end, dropping the database worked instantly.

今天就来处理这个问题。在任何模式信息表中都没有两个受影响的表的迹象。我搜索了各种系统数据库寻找FK，但没有成功。最后，删除数据库立即生效。

#4

-1

I had a similar error, what mysql requires is that you have both key and foreign, type and size match, for eg. person.id int (10) must mapped to service_customer.person_id in (10) if the type and size differ, mysql will complain.

我有一个类似的错误，mysql要求您同时拥有key和foreign, type和size匹配，例如。的人。id int(10)必须映射到service_customer。如果类型和大小不同，mysql会抱怨。

#1