将自动增量标识添加到 oracle 中非空的现有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23102958/
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
Adding auto increment identity to existing table in oracle which is not empty
提问by Ali
I was wondering how can I add an identity column to existing oracle table? I am using oracle 11g. Suppose I have a table named DEGREE and I am going to add an identity column to that.
我想知道如何向现有的 oracle 表添加标识列？我正在使用 oracle 11g。假设我有一个名为 DEGREE 的表，我将向其中添加一个标识列。
FYI table is not empty.
仅供参考的表不是空的。
回答by Andreas Fester
You can not do it in one step. Instead,
你不能一步完成。反而，
Alter the table and add the column (without primary key constraint)
ALTER TABLE DEGREE ADD (Ident NUMBER(10));Fill the new column with data which will fulfill the primary key constraint (unique/not null), e.g. like
UPDATE DEGREE SET Ident=ROWNUM;Alter the table and add the constraint to the column
ALTER TABLE DEGREE MODIFY (Ident PRIMARY KEY);
更改表并添加列（无主键约束）
ALTER TABLE DEGREE ADD (Ident NUMBER(10));用将满足主键约束（唯一/非空）的数据填充新列，例如
UPDATE DEGREE SET Ident=ROWNUM;更改表并将约束添加到列
ALTER TABLE DEGREE MODIFY (Ident PRIMARY KEY);
After that is done, you can set up a SEQUENCEand a BEFORE INSERTtrigger to automatically set the id value for new records.
完成后，您可以设置一个SEQUENCE和一个BEFORE INSERT触发器来自动设置新记录的 id 值。
回答by Florin Ghita
add the column
alter table table_name add (id INTEGER);create a sequence
table_name_id_seqwithstart withclause, using number of rows in the table + 1 or another safe value(we don't want duplicate ids);lock the table (no inserts)
alter table table_name lock exclusive mode;fill the column
update table_name set id = rownum; --or another logicadd a trigger to automaticaly put the id on insert using the sequence(you can find examples on internet, for example this answer)
添加列
alter table table_name add (id INTEGER);创建一个
table_name_id_seq带有start with子句的序列，使用表中的行数 + 1 或其他安全值（我们不想要重复的 id）；锁定表（无插入）
alter table table_name lock exclusive mode;填一栏
update table_name set id = rownum; --or another logic添加一个触发器以使用序列自动将 id 放在插入上（您可以在互联网上找到示例，例如这个答案）
When you'll fire the create trigger the lock will be released. (it automatically commits). Also, you may add unique constraint on the id column, it is best to do so.
当您触发创建触发器时，锁将被释放。（它自动提交）。此外，您可以在 id 列上添加唯一约束，最好这样做。
回答by William Robertson
From Oracle 12c you would use an identity column.
从 Oracle 12c 开始，您将使用标识列。
For example, say your table is called demoand has 3 columns and 100 rows:
例如，假设您的表被调用demo并且有 3 列和 100 行：
create table demo (col1, col2, col3)
as
select dbms_random.value(1,10), dbms_random.value(1,10), dbms_random.value(1,10)
from dual connect by rownum <= 100;
You could add an identity column using:
您可以使用以下方法添加标识列：
alter table demo add demo_id integer generated by default on null as identity;
update demo set demo_id = rownum;
Then reset the internal sequence to match the data and prevent manual inserts:
然后重置内部序列以匹配数据并防止手动插入：
alter table demo modify demo_id generated always as identity start with limit value;
and define it as the primary key:
并将其定义为主键：
alter table demo add constraint demo_pk primary key (demo_id);
This leaves the new column at the end of the column list, which shouldn't normally matter (except for tables with a large number of columns and row chaining issues), but it looks odd when you describe the table. However, we can at least tidy up the dictionary order using the invisible/visible hack:
这会将新列留在列列表的末尾，这通常无关紧要（除了具有大量列和行链接问题的表），但是当您描述该表时，它看起来很奇怪。但是，我们至少可以使用 invisible/visible hack 整理字典顺序：
SQL> desc demo
Name Null? Type
-------------------------------- -------- ----------------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
DEMO_ID NOT NULL NUMBER(38)
begin
for r in (
select column_name from user_tab_columns c
where c.table_name = 'DEMO'
and c.column_name <> 'DEMO_ID'
order by c.column_id
)
loop
execute immediate 'alter table demo modify '||r.column_name||' invisible';
execute immediate 'alter table demo modify '||r.column_name||' visible';
end loop;
end;
/
SQL> desc demo
Name Null? Type
-------------------------------- -------- ----------------------
DEMO_ID NOT NULL NUMBER(38)
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
One thing you can't do (as of Oracle 18.0) is alter an existing column to make it into an identity column, so you have to either go through a process like the one above but copying the existing values and finally dropping the old column, or else define a new table explicitly with the identity column in place and copy the data across in a separate step. Otherwise you'll get:
您不能做的一件事（从 Oracle 18.0 开始）是更改现有列以使其成为标识列，因此您必须执行上述过程，但复制现有值并最终删除旧列，或者明确定义一个新表，其中标识列就位，并在单独的步骤中复制数据。否则你会得到：
-- DEMO_ID column exists but is currently not an identity column:
alter table demo modify demo_id generated by default on null as identity start with limit value;
-- Fails with:
ORA-30673: column to be modified is not an identity column
