如何在Oracle上使用AUTO_INCREMENT创建ID？

本文翻译自：How to create id with AUTO_INCREMENT on Oracle?

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g. 直到并包括11g版，Oracle中似乎都没有AUTO_INCREMENT的概念。

How can I create a column that behaves like auto increment in Oracle 11g? 如何在Oracle 11g中创建行为类似于自动增量的列？

---

#1楼

参考：https://stackoom.com/question/lOhN/如何在Oracle上使用AUTO-INCREMENT创建ID

---

#2楼

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g . 从Oracle 11g开始，Oracle中没有诸如“ auto_increment”或“ identity”列之类的东西。 However, you can model it easily with a sequence and a trigger: 但是，您可以使用序列和触发器轻松对其进行建模：

Table definition: 表定义：

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq START WITH 1;

Trigger definition: 触发定义：

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE: 更新：

IDENTITY column is now available on Oracle 12c: Oracle 12c上现在提供了IDENTITY列：

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column ( GENERATED ALWAYS ) (again, Oracle 12c+ only) 或指定起始值和增量值，也防止任何插入到身份列中（ GENERATED ALWAYS ）（同样，仅适用于Oracle 12c +）

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value: 另外，Oracle 12还允许使用序列作为默认值：

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

---

#3楼

SYS_GUID returns a GUID-- a globally unique ID. SYS_GUID返回GUID-全局唯一ID。 A SYS_GUID is a RAW(16) . SYS_GUID是RAW(16) 。 It does not generate an incrementing numeric value. 它不会生成递增的数值。

If you want to create an incrementing numeric key, you'll want to create a sequence. 如果要创建递增数字键，则需要创建一个序列。

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

You would then either use that sequence in your INSERT statement 然后，您可以在INSERT语句中使用该序列

INSERT INTO name_of_table( primary_key_column, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

Or you can define a trigger that automatically populates the primary key value using the sequence 或者，您可以定义一个触发器，使用该序列自动填充主键值

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

If you are using Oracle 11.1 or later, you can simplify the trigger a bit 如果您使用的是Oracle 11.1或更高版本，则可以简化触发器

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.primary_key_column := name_of_sequence.nextval;
END;

If you really want to use SYS_GUID 如果您真的想使用SYS_GUID

CREATE TABLE table_name (
  primary_key_column raw(16) default sys_guid() primary key,
  <<other columns>>
)

---

#4楼

oracle has sequences AND identity columns in 12c oracle在12c中具有序列AND身份列

http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php#identity-columns http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php#identity-columns

I found this but not sure what rdb 7 is http://www.oracle.com/technetwork/products/rdb/0307-identity-columns-128126.pdf 我发现了这一点，但不确定什么是rdb 7 http://www.oracle.com/technetwork/products/rdb/0307-identity-columns-128126.pdf

---

#5楼

Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column. Oracle Database 12c引入了Identity，这是一个自动增量（系统生成的）列。 In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger. 在以前的数据库版本（直到11g）中，通常通过创建序列和触发器来实现身份。 From 12c onward, you can create your own Table and define the column that has to be generated as an Identity. 从12c开始，您可以创建自己的表并定义必须作为身份生成的列。

The following article explains how to use it: 下面的文章解释了如何使用它：

Identity columns - A new entry in Oracle Database 12c 标识列-Oracle Database 12c中的新条目

---

#6楼

Assuming you mean a column like the SQL Server identity column? 假设您指的是SQL Server标识列之类的列？

In Oracle, you use a SEQUENCE to achieve the same functionality. 在Oracle中，您可以使用SEQUENCE实现相同的功能。 I'll see if I can find a good link and post it here. 我将查看是否可以找到一个很好的链接并将其发布在这里。

Update: looks like you found it yourself. 更新：看起来像您自己找到的。 Here is the link anyway: http://www.techonthenet.com/oracle/sequences.php 无论如何，这里是链接： http : //www.techonthenet.com/oracle/sequences.php