(错误)InnoDB MySQL错误1025，错误码150外键

本文介绍了(错误)InnoDB MySQL错误1025，错误码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;

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

MySQL错误1025

(错误)InnoDB MySQL错误1025，错误码150外键

问题描述

推荐答案