Oracle 12c:如何修改现有的主键列为标识列?

Question

我有一个表，它包含一个主键列，从应用程序自动增加。如何将列修改为Oracle 12c中的标识列 ?

下面提供了一个示例案例-

create table tmp_identity (
   id number(100) primary key,
   value varchar2(100)
);

假设我们用以下数据填充表-

ID        VALUE
---------------
1         Sample 1
2         Sample 2
3         Sample 3

我们打算做的是把这个id列变成一个标识列，这将-

• 自动递增1
• 从4
开始

我该怎么做?如果这是不可能的，那么是否有可用的变通方法?

Answer 1

不能将现有列转换为真正的标识列，但是可以通过使用序列作为列的默认值来获得类似的行为。

create sequence seq_tmp_identity_id
  start with 4
  increment by 1;

然后使用:

alter table tmp_identity 
   modify id 
   default seq_tmp_identity_id.nextval;

使列使用该序列作为默认值。如果您愿意，您可以使用default on null覆盖插入期间提供的显式null值(这是您可以获得的最接近标识列)

如果你想要一个真实的标识列，你需要删除当前的id列，然后重新添加它作为标识列:

alter table tmp_identity drop column id;
alter table tmp_identity 
     add id number(38) 
     generated always as identity;

请注意，在本例中不应该添加start with 4，以便所有行都获得一个新的唯一编号

Answer 2

您可以使用以下查询

将列从id重命名为identity

ALTER TABLE tmp_identity
RENAME COLUMN id TO identity; 

可以使用sequence.

来自动增加1并从4开始。

CREATE SEQUENCE identity_incre
MINVALUE 4
START WITH 4
INCREMENT BY 1;

在查询中使用identity_incre序列

INSERT INTO suppliers
(identity, VALUE)
VALUES
(identity_incre.NEXTVAL, 'sample4');