Oracle 12c：如何将现有的主键列修改为标识列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32976743/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 12c: How can I modify an existing primary key column to an identity column?
提问by Samiul Al Hossaini
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 columnin Oracle 12c?
我有一个表，其中包含一个从应用程序自动递增的主键列。如何将列修改为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
What we are planning to do is to turn this idcolumn into an identity columnwhich will-
我们计划做的是把这个id专栏变成一个身份专栏，它将——
- Auto increment by 1
- Start from 4
- 自动递增 1
- 从 4 开始
How can I do it? If it is not possible, then is there any work-around available for this?
我该怎么做？如果不可能，那么是否有任何可用的解决方法？
回答by a_horse_with_no_name
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;
Then use:
然后使用：
alter table tmp_identity
modify id
default seq_tmp_identity_id.nextval;
to make the column use the sequence as a default value. If you want you can use default on nullto overwrite an explicit nullvalue supplied during insert (this is as close as you can get to an identity column)
使列使用序列作为默认值。如果您愿意，您可以使用default on null覆盖null插入期间提供的显式值（这与您可以获得的标识列尽可能接近）
If you want a realidentity column you will need to drop the current idcolumn and then re-add it as an identity column:
如果您想要一个真实的身份列，您需要删除当前id列，然后将其重新添加为身份列：
alter table tmp_identity drop column id;
alter table tmp_identity
add id number(38)
generated always as identity;
Note that you shouldn't add the start with 4in this case so that all rows get a new unique number
请注意，start with 4在这种情况下您不应该添加 ，以便所有行都获得一个新的唯一编号
回答by ABHISHEK RANA
You can rename the column from idto identityby using following query
您可以使用以下查询将列重命名为idtoidentity
ALTER TABLE tmp_identity
RENAME COLUMN id TO identity;
To auto increment by 1and start from 4, we can use sequence.
要自动递增1并从 开始4，我们可以使用序列。
CREATE SEQUENCE identity_incre
MINVALUE 4
START WITH 4
INCREMENT BY 1;
To use the identity_incresequence in your query
identity_incre在查询中使用序列
INSERT INTO suppliers
(identity, VALUE)
VALUES
(identity_incre.NEXTVAL, 'sample4');
