mysql - 更改也是另一个表的外键的主键列 - Mysql

Question

我有以下两张表。他们都有数据。我想将 charge_date 从日期修改为日期时间。 Mysql 不允许我这样做。 我想在不删除并重新创建现有表的情况下执行此操作。

CREATE TABLE `cmpny_charges` (
  `company_id` int(10) unsigned NOT NULL,
  `charge_id` varchar(45) NOT NULL,
  `charge_date` date NOT NULL,
  `charge_amt` double NOT NULL,
  `chargeholder_id` varchar(20) NOT NULL,
  `filing_no` int(10) unsigned DEFAULT NULL,
  `registration_date` date DEFAULT NULL,
  `srn` varchar(10) DEFAULT NULL,
  `charge_type` char(1) NOT NULL,
  `updated_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `doc_no` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`company_id`,`charge_id`,`charge_date`),
  KEY `cmpny_charges_FK_2` (`chargeholder_id`),
  KEY `cmpny_charges_FK_1` (`filing_no`),
  CONSTRAINT `cmpny_charges_FK_2` FOREIGN KEY (`chargeholder_id`) REFERENCES `chargeholder_dtls` (`chargeholder_id`),
  CONSTRAINT `cmpny_charges_FK_3` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `cmpny_charge_dtls` (
  `company_id` int(10) unsigned NOT NULL,
  `charge_id` varchar(45) NOT NULL,
  `charge_date` date NOT NULL,
  `modification_part` text,
  `part_property_charged` text,
  `filing_date` datetime DEFAULT NULL,
  `srn` varchar(9) DEFAULT NULL,
  `rate_of_interest` text,
  `repayment_terms` text,
  `margin` text,
  `nature_instrument` text,
  PRIMARY KEY (`company_id`,`charge_id`,`charge_date`),
  CONSTRAINT `FK_cmpny_charge_dtls_1` FOREIGN KEY (`company_id`, `charge_id`, `charge_date`) REFERENCES `cmpny_charges` (`company_id`, `charge_id`, `charge_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这是我想要执行的更改脚本

set foreign_key_checks=0;
lock tables cmpny_charges write, cmpny_charge_dtls write;
ALTER TABLE `cmpny_charge_dtls` CHANGE `charge_date` `charge_date` datetime not null;
ALTER TABLE `cmpny_charges` CHANGE `charge_date` `charge_date` datetime not null;
unlock tables ;
set foreign_key_checks =1;

当我执行show engine innodb status时出现如下错误

最新外键错误

140313 19:54:03 Error in foreign key constraint of table csmart/cmpny_charge_dtls: 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 the ones in table. Constraint: ,
CONSTRAINT "FK_cmpny_charge_dtls_1" FOREIGN KEY ("company_id", "charge_id", "charge_date") REFERENCES "cmpny_charges" ("company_id", "charge_id", "charge_date") The index in the foreign key in table is "PRIMARY" See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. InnoDB: Renaming table csmart. to csmart.cmpny_charges failed!

这些是注意事项

1. 我不知道要暂时删除它们的约束的名称。相同的约束可以以不同的名称出现。 (我需要更新该数据库的多个副本)

2. 我想在不创建临时表、将数据复制到其中然后将它们重命名为原始表的情况下执行此操作。当多个 session 登录到同一数据库尝试同时执行相同的操作时，这会导致问题，从而导致数据库处于不一致的状态。

Answer 1

添加一个新列，将数据复制到其中。然后修改外键以指向新列，并删除旧列。

alter table cmpny_charges add charge_date2 datetime;
update cmpny_charges set charge_date2= charge_date;

-- (I am used to do this via GUI, sorry...)
-- drop foreing key
---modify the primary key 
-- recreate the foreing key

alter table cmpny_charges drop charge_date;