将自动增量标识添加到不为空的oracle中的现有表中 [英] Adding auto increment identity to existing table in oracle which is not empty

问题描述

我想知道如何将标识列添加到现有的oracle表中?我正在使用oracle 11g.假设我有一个名为DEGREE的表，我将向其中添加一个标识列.

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.

FYI表不为空.

推荐答案

您不能一步一步完成.相反，

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);

完成此操作后，您可以设置SEQUENCE和BEFORE INSERT触发器来自动设置新记录的id值.

After that is done, you can set up a SEQUENCE and a BEFORE INSERT trigger to automatically set the id value for new records.

这篇关于将自动增量标识添加到不为空的oracle中的现有表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！