我需要更改 PK 列上的名称,这与另一个表有关系,这意味着它们也是 FK,所以这就是我正在做的:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ALLOW_INVALID_DATES';
#Drop FKs and IDX
ALTER TABLE `pdone`.`reps` DROP FOREIGN KEY `FK_485DE7B033B9A304`;
ALTER TABLE `pdone`.`reps` DROP INDEX `IDX_485DE7B033B9A304` ;
ALTER TABLE `pdone`.`targets` DROP FOREIGN KEY `FK_AF431E1333B9A304`;
ALTER TABLE `pdone`.`targets` DROP INDEX `IDX_AF431E1333B9A304` ;
ALTER TABLE `pdone`.`targets_brands_xref` DROP FOREIGN KEY `FK_85785F3E43B5F743`;
ALTER TABLE `pdone`.`targets_brands_xref` DROP INDEX `IDX_85785F3E43B5F743` ;
ALTER TABLE `pdone`.`territories_brands_xref` DROP FOREIGN KEY `FK_2265AB7933B9A304`;
ALTER TABLE `pdone`.`territories_brands_xref` DROP INDEX `IDX_2265AB7933B9A304` ;
#Change PK names
ALTER TABLE `pdone`.`reps` CHANGE COLUMN `veeva_rep_id` `rep_id` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT '';
ALTER TABLE `pdone`.`territories` CHANGE COLUMN `veeva_territory_id` `territory_id` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT '' ;
ALTER TABLE `pdone`.`targets` CHANGE COLUMN `veeva_account_id` `target_id` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT '' ;
#Add FK back based on new column names
ALTER TABLE `pdone`.`reps` ADD CONSTRAINT `FK_485DE7B033B9A304` FOREIGN KEY(`territories_id`) REFERENCES `pdone`.`territories`(`territory_id`);
ALTER TABLE `pdone`.`reps` ADD INDEX `IDX_485DE7B033B9A304` (`territories_id`);
ALTER TABLE `pdone`.`targets` ADD CONSTRAINT `FK_AF431E1333B9A304` FOREIGN KEY(`territories_id`) REFERENCES `pdone`.`territories`(`territory_id`);
ALTER TABLE `pdone`.`targets` ADD INDEX `IDX_AF431E1333B9A304` (`territories_id`);
ALTER TABLE `pdone`.`targets_brands_xref` ADD CONSTRAINT `FK_85785F3E43B5F743` FOREIGN KEY(`targets_id`) REFERENCES `pdone`.`targets`(`target_id`);
ALTER TABLE `pdone`.`targets_brands_xref` ADD INDEX `IDX_85785F3E43B5F743` (`target_id`);
ALTER TABLE `pdone`.`territories_brands_xref` ADD CONSTRAINT `FK_2265AB7933B9A304` FOREIGN KEY(`territories_id`) REFERENCES `pdone`.`territories`(`territory_id`);
ALTER TABLE `pdone`.`territories_brands_xref` ADD INDEX `IDX_2265AB7933B9A304` (`territories_id`);
#Drop lastSyncAt column
ALTER TABLE `pdone`.`reps` DROP COLUMN `lastSyncAt`;
ALTER TABLE `pdone`.`targets` DROP COLUMN `lastSyncAt`;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
但我收到此错误:
Executing:
ALTER TABLE `pdone`.`reps`
CHANGE COLUMN `veeva_rep_id` `rep_id` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT '' ;
Operation failed: There was an error while applying the SQL script to the database.
ERROR 1025: Error on rename of './pdone/#sql-85a_1b' to './pdone/reps' (errno: 150)
SQL Statement:
ALTER TABLE `pdone`.`reps`
CHANGE COLUMN `veeva_rep_id` `rep_id` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT ''
这是表的CREATE
语句pdone.reps
:
CREATE TABLE `reps` (
`veeva_rep_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`territories_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`display_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`avatar_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`rep_type` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`username` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`first` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`last` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`bio` longtext COLLATE utf8_unicode_ci,
`phone` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`inactive` tinyint(1) NOT NULL,
`lastLoginAt` datetime DEFAULT NULL,
`lastSyncAt` datetime NOT NULL,
`repTokenId` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`createdAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`veeva_rep_id`),
KEY `IDX_485DE7B033B9A304` (`territories_id`),
CONSTRAINT `FK_485DE7B033B9A304` FOREIGN KEY (`territories_id`) REFERENCES `territories` (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
为什么我会收到该错误?任何人都可以在这里给我一些帮助吗?
这是一个外键错误 (
Error: 1025 | errno: 150
),您可以使用以下命令找到它perror
:您可以获得有关失败原因的更多详细信息,
SHOW ENGINE INNODB STATUS;
并搜索LATEST FOREIGN KEY ERROR
包含有关错误详细信息的部分。我认为您的
veeva_rep_id
专栏有问题。