mysql - 添加一列到复合主键，该列也是其他表中的外键

Question

我必须向表中的主键添加一列，其中主键由另外两列组成。
最重要的是，该表的复合主键是其他表中的外部键，并且第一个表的列在第三个表中创建了复合主键，因此我也需要更新其他表。
总而言之，我有一个场景如下:

Table A (column_A,column_B,column_C,column_D) Primary Key: (column_A,column_B)
Table B (column_E,column_A,column_B,column_F) Primary Key: (column_E), Foreign Key (column_A,column_B)
Table C (column_G,column_A,column_B,column_G) Primary Key: (column_G,column_B), Foreign Key (column_A,column_B)

虽然我想实现:

Table A (column_A,column_B,column_New,column_C,column_D) Primary Key: (column_A,column_B,column_New)
Table B (column_E,column_A,column_B,column_New,column_F) Primary Key: (column_E), Foreign Key (column_A,column_B,column_New)
Table C (column_G,column_A,column_B,column_New,column_G) Primary Key: (column_G,column_B,column_New), Foreign Key (column_A,column_B,column_New)

我怎样才能实现这一目标？

Answer 1

执行以下步骤:

删除主键和外键(保持 table_b 的 PK 不变)

alter table TABLE_A drop primary key; 
alter table TABLE_C drop primary key; 

alter table TABLE_B drop foreign key FOREIGN_KEY_NAME_B; 
alter table TABLE_C drop foreign key FOREIGN_KEY_NAME_C;

接下来，在各处添加column_new...

alter table TABLE_A add column COLUMN_NEW <column_spec_here> after COLUMN_B;
alter table TABLE_B add column COLUMN_NEW <column_spec_here> after COLUMN_B;
alter table TABLE_C add column COLUMN_NEW <column_spec_here> after COLUMN_B;

接下来，重建主键和外键...

alter table TABLE_A add primary key (COLUMN_A, COLUMN_B, COLUMN_NEW);
alter table TABLE_C add primary key (COLUMN_G, COLUMN_B, COLUMN_NEW);

alter table TABLE_B add foreign key NEW_FOREIGN_KEY_NAME_B (COLUMN_A, COLUMN_B, COLUMN_NEW) references TABLE_A (COLUMN_A, COLUMN_B, COLUMN_NEW);
alter table TABLE_C add foreign key NEW_FOREIGN_KEY_NAME_C (COLUMN_A, COLUMN_B, COLUMN_NEW) references TABLE_A (COLUMN_A, COLUMN_B, COLUMN_NEW);