Início / dba / Perguntas / 107049
Accepted
ReynierPM
Asked: 2015-07-16 06:35:03 +0800 CST

Renomeie os nomes das colunas PK quando forem FK em outras tabelas

  • 772

Eu preciso mudar os nomes nas colunas PK e isso tem relações com outras tabelas, o que significa que elas também são FK, então é isso que estou fazendo:

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;

Mas estou recebendo este erro:

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

Esta é a CREATEdeclaração para a pdone.repstabela:

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;

Por que estou recebendo esse erro? Alguém pode me dar uma ajuda aqui?

index foreign-key

1 respostas

  1. Best Answer

    É um erro de chave estrangeira ( Error: 1025 | errno: 150) que você pode descobrir usando perror:

    root@onare:/home/onare# perror 1025
MySQL error code 1025 (ER_ERROR_ON_RENAME): Error on rename of '%-.210s' to '%-.210s' (errno: %d)
root@onare:/home/onare# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

    Você pode obter mais detalhes sobre o que falhou SHOW ENGINE INNODB STATUS;e pesquisar a LATEST FOREIGN KEY ERRORseção que contém detalhes sobre o que está errado.

    Acho que sua veeva_rep_idcoluna tem o problema.

    • 1

relate perguntas