达梦默认情况下主键为聚簇主键,设置主键之后,主键类型无法直接修改。
1.需先新增临时索引。
2.如果主键有自增,要先删除自增属性(文章后续没有添加此部分,根据实际情况选择)
ALTER TABLE SYS_USER DROP IDENTITY;
单表修改主键类型
######### 新增临时索引
CREATE CLUSTER INDEX T_TEAM_DISPATCH_TASK_DDDXXX ON T_TEAM_DISPATCH_TASK(AQJCR);
COMMIT;
### 先执行上一条命令,执行后 constraint 名称会发生变化
######### 删除主键约束
ALTER TABLE T_TEAM_DISPATCH_TASK DROP constraint T_TEAM_DISPATCH_TASK_PK ;
######### 修改主键类型
ALTER TABLE T_TEAM_DISPATCH_TASK MODIFY ( ID INT ) ;
######### 删除临时索引
DROP INDEX T_TEAM_DISPATCH_TASK_DDDXXX ;
######### 添加主键约束
ALTER TABLE T_TEAM_DISPATCH_TASK ADD CONSTRAINT T_TEAM_DISPATCH_TASK_PK PRIMARY KEY (ID);
COMMIT;
多表批量修改主键类型
添加索引
WITH A AS (SELECT TABLE_NAME, MIN(COLUMN_NAME) COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME <> 'ID'
AND TABLE_NAME NOT IN('T_TEAM_ID','T_TEAM_FKPT_RZY','T_TEAM_FKPT_ZJH')
GROUP BY TABLE_NAME)
select 'CREATE CLUSTER INDEX ' || A.TABLE_NAME || '_DDDXXX' || ' ON ' || A.TABLE_NAME || '(' || COLUMN_NAME ||
');'
FROM A
UNION
SELECT 'COMMIT;' FROM DUAL;
修改主键
WITH A AS (SELECT TABLE_NAME, MIN(COLUMN_NAME) COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME <> 'ID'
AND TABLE_NAME NOT IN ('T_TEAM_ID', 'T_TEAM_FKPT_RZY', 'T_TEAM_FKPT_ZJH')
GROUP BY TABLE_NAME)
select 'ALTER TABLE ' || A.TABLE_NAME || ' DROP constraint ' || C.CONSTRAINT_NAME || ' ;'
FROM A
LEFT JOIN (select CONSTRAINT_NAME, TABLE_NAME from USER_CONSTRAINTS CONS WHERE CONS.CONSTRAINT_TYPE = 'P') C
ON C.TABLE_NAME = A.TABLE_NAME
UNION ALL
select 'ALTER TABLE ' || A.TABLE_NAME || ' MODIFY ( ID INT ) ;'
-- select 'ALTER TABLE ' || A.TABLE_NAME || ' MODIFY ( ID VARCHAR(50) ) ;'
FROM A
UNION ALL
select 'DROP INDEX ' || A.TABLE_NAME || '_DDDXXX ;'
FROM A
UNION ALL
select 'ALTER TABLE ' || A.TABLE_NAME || ' ADD CONSTRAINT ' || A.TABLE_NAME || '_PK PRIMARY KEY (ID);'
FROM A
UNION
SELECT 'COMMIT;'
FROM DUAL;