1、db2修改主键
--第一步:增加列object_id
alter table SCM_ORG_MLTY_MACRO_INDEX add object_id varchar(30);
--第二部:将历史数据的object_id填充为com_id
update SCM_ORG_MLTY_MACRO_INDEX set object_id =com_id;
commit;
--第三步:将object_id置为非空
alter table SCM_ORG_MLTY_MACRO_INDEX alter object_id set not null;
--第四步:删除主键
ALTER TABLE SCM_ORG_MLTY_MACRO_INDEX DROP primary key ;
--第五步:重组表结构
call sysproc.admin_cmd ( ' reorg table db2inst1.SCM_ORG_MLTY_MACRO_INDEX' );
--第六步:增加主键
ALTER TABLE SCM_ORG_MLTY_MACRO_INDEX ADD CONSTRAINT
PK_SCM_ORG_MLTY_MACRO_INDEX_object_id PRIMARY KEY(COM_ID,object_id, DATE1, INDEX_ID);
2、oracle修改主键
--第一步:增加列object_id
alter table SCM_ORG_MLTY_MACRO_INDEX add object_id varchar2(30);
--第二部:将历史数据的object_id填充为com_id
update SCM_ORG_MLTY_MACRO_INDEX set object_id =com_id;
commit;
--第三步:将object_id置为非空
alter table SCM_ORG_MLTY_MACRO_INDEX modify object_id varchar2(30) not null;
--第四步:删除主键
ALTER TABLE SCM_ORG_MLTY_MACRO_INDEX DROP primary key;
--第五步:增加主键
ALTER TABLE SCM_ORG_MLTY_MACRO_INDEX ADD (CONSTRAINT PK_SCM_ORG_MLTY PRIMARY KEY(COM_ID,object_id, DATE1, INDEX_ID));
alter table SCM_ORG_MLTY_MACRO_INDEX add object_id varchar2(30);
--第二部:将历史数据的object_id填充为com_id
update SCM_ORG_MLTY_MACRO_INDEX set object_id =com_id;
commit;
--第三步:将object_id置为非空
alter table SCM_ORG_MLTY_MACRO_INDEX modify object_id varchar2(30) not null;
--第四步:删除主键
ALTER TABLE SCM_ORG_MLTY_MACRO_INDEX DROP primary key;
--第五步:增加主键
ALTER TABLE SCM_ORG_MLTY_MACRO_INDEX ADD (CONSTRAINT PK_SCM_ORG_MLTY PRIMARY KEY(COM_ID,object_id, DATE1, INDEX_ID));