db2、oracle修改主键

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));