oracle 给现有表添加自增主键

Add a auto increment primary key to existing table in oracle

提问by mallikarjun

I want to add a new auto increment primary column to a existing table which has data. How do I do that?

我想向包含数据的现有表添加一个新的自动增量主列。我怎么做？

I first added a column and then try to add a sequence after that, I lost how to insert and make that column as primary key.

我首先添加了一列，然后尝试在此之后添加一个序列，我失去了如何插入并将该列设为主键的方法。

采纳答案by alfasin

Say your table is called t1and your primary-key is called id
First, create the sequence:

假设你的表被调用t1，你的主键被调用id
首先，创建序列：

create sequence t1_seq start with 1 increment by 1 nomaxvalue; 

Then create a trigger that increments upon insert:

然后创建一个在插入时递增的触发器：

create trigger t1_trigger
before insert on t1
for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

回答by Justin Cave

If you have the column and the sequence, you first need to populate a new key for all the existing rows. Assuming you don't care which key is assigned to which row

如果您有列和序列，您首先需要为所有现有行填充一个新键。假设您不关心哪个键分配给哪一行

UPDATE table_name
   SET new_pk_column = sequence_name.nextval;

Once that's done, you can create the primary key constraint (this assumes that either there is no existing primary key constraint or that you have already dropped the existing primary key constraint)

完成后，您可以创建主键约束（假设没有现有的主键约束或您已经删除了现有的主键约束）

ALTER TABLE table_name
  ADD CONSTRAINT pk_table_name PRIMARY KEY( new_pk_column )

If you want to generate the key automatically, you'd need to add a trigger

如果要自动生成密钥，则需要添加触发器

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.new_pk_column := sequence_name.nextval;
END;

If you are on an older version of Oracle, the syntax is a bit more cumbersome

如果您使用的是旧版本的 Oracle，则语法会比较繁琐

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_pk_column
    FROM dual;
END;

回答by Hermit

Snagged from Oracle OTN forums

摘自Oracle OTN 论坛

Use alter table to add column, for example:

使用 alter table 添加列，例如：

alter table tableName add(columnName NUMBER);

Then create a sequence:

然后创建一个序列：

CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE;

and, the use updateto insert values in column like this

并且，用于update在这样的列中插入值

UPDATE tableName SET columnName = seq_test_id.NEXTVAL

回答by Hirosh Wickramasuriya

You can use the Oracle Data Modelerto create auto incrementing surrogate keys.

您可以使用Oracle Data Modeler创建自动递增的代理键。

Step 1. - Create a Relational Diagram

步骤 1. - 创建关系图

You can first create a Logical Diagram and Engineer to create the Relational Diagram or you can straightaway create the Relational Diagram.

您可以先创建逻辑图和工程师来创建关系图，也可以直接创建关系图。

Add the entity (table) that required to have auto incremented PK, select the type of the PK as Integer.

添加需要自增PK的实体（表），PK类型选择Integer。

Step 2. - Edit PK Column Property

第 2 步。 - 编辑 PK 列属性

Get the properties of the PK column. You can double click the name of the column or click on the 'Properties' button.

获取 PK 列的属性。您可以双击列的名称或单击“属性”按钮。

Column Properties dialog box appears.

列属性对话框出现。

Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.

选择常规选项卡（第一次默认选择）。然后选中“自动增量”和“标识列”复选框。

Step 3. - Additional Information

第 3 步。 - 附加信息

Additional information relating to the auto increment can be specified by selecting the 'Auto Increment' tab.

可以通过选择“自动增量”选项卡来指定与自动增量相关的附加信息。

• Start With
• Increment By
• Min Value
• Max Value
• Cycle
• Disable Cache
• Order
• Sequence Name
• Trigger Name
• Generate Trigger

• 从...开始
• 递增
• 最小值
• 最大值
• 循环
• 禁用缓存
• 命令
• 序列名称
• 触发器名称
• 生成触发器

It is usually a good idea to mention the sequence name, so that it will be useful in PL/SQL.

提及序列名称通常是一个好主意，以便它在 PL/SQL 中很有用。

Click OK (Apply) to the Column Properties dialog box.

单击确定（应用）到列属性对话框。

Click OK (Apply) to the Table Properties dialog box.

单击“确定（应用）”到“表属性”对话框。

Table appears in the Relational Diagram.

表出现在关系图中。