Oracle 12c:如何将现有的主键列修改为标识列? [英] Oracle 12c: How can I modify an existing primary key column to an identity column?

问题描述

我有一个表，其中包含一个从应用程序自动递增的主键列.如何在 Oracle 12c 中将该列修改为身份列?

I have a table which contains a primary key column which is auto incremented from application. How can I modify the column to be an identity column in Oracle 12c?

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

A sample case is provided below-

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

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

Say we populated the table with following data-

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

我们计划做的是将这个 id 列变成一个 identity 列，这将 -

What we are planning to do is to turn this id column into an identity column which will-

• 自动递增 1
• 从4开始

我该怎么做?如果不可能，那么是否有任何可用的解决方法?

How can I do it? If it is not possible, then is there any work-around available for this?

推荐答案

您无法将现有列转换为真实标识列，但您可以通过使用序列作为列的默认值来获得类似的行为.

You can't turn an existing column it into a real identity column, but you can get a similar behaviour by using a sequence as the default for the column.

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 值(这与您可以获得的标识列尽可能接近)

to make the column use the sequence as a default value. If you want you can use default on null to overwrite an explicit null value supplied during insert (this is as close as you can get to an identity column)

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

If you want a real identity column you will need to drop the current id column and then re-add it as an identity column:

alter table tmp_identity drop column id;

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

请注意，在这种情况下，您不应添加 start with 4 以便所有行都获得一个新的唯一编号

Note that you shouldn't add the start with 4 in this case so that all rows get a new unique number

这篇关于Oracle 12c:如何将现有的主键列修改为标识列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！