我有一张表,想要更改主键。以下是该表的定义。
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 '$$
现在,当我第一次尝试时:
ALTER TABLE `tbl_customer` DROP PRIMARY KEY;
我的主键是Id
。当我试图删除它时,出现了以下问题:
Error Code: 1025. Error on rename of './services/#sql-29a_218cc7f' to './services/tbl_customer' (errno: 150)
因此,我删除了所有引用该表和列的 FOREIGN KEY
约束,但仍然收到相同的错误。我还查看了 SHOW ENGINE INNODB STATUS
并发现以下内容:
------------------------
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
不存在(虽然曾经存在过,但在我尝试进行更改的几周前已被删除)。因此,我继续进行了...
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。
编辑:这个问题没有解决,只能绕过去了。
当我们将数据库迁移到一个更新的服务器(相同的mysql版本)时,这个问题才得以纠正,似乎存在一个损坏/幽灵引用到幽灵外键(fk_tbl_employee_shift_tbl_customer1),它阻止了列被删除。由于这个损坏/幽灵fk在新服务器中不存在,所以我可以毫无问题地删除该列。我的猜测是这是一个bug,但不幸的是我无法再现它。