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.
How can I create a column that behaves like auto increment in Oracle 11g?
There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:
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;
CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROW BEGIN SELECT dept_seq.NEXTVAL INTO :new.id FROM dual; END; /
IDENTITY column is now available on Oracle 12c:
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)
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:
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));
Read more... Read less...
SYS_GUID returns a GUID-- a globally unique ID. A
SYS_GUID is a
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 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
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
CREATE TABLE table_name ( primary_key_column raw(16) default sys_guid() primary key, <<other columns>> )
In Oracle 12c onward you could do something like,
CREATE TABLE MAPS ( MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, MAP_NAME VARCHAR(24) NOT NULL, UNIQUE (MAP_ID, MAP_NAME) );
And in Oracle (Pre 12c).
-- create table CREATE TABLE MAPS ( MAP_ID INTEGER NOT NULL , MAP_NAME VARCHAR(24) NOT NULL, UNIQUE (MAP_ID, MAP_NAME) ); -- create sequence CREATE SEQUENCE MAPS_SEQ; -- create tigger using the sequence CREATE OR REPLACE TRIGGER MAPS_TRG BEFORE INSERT ON MAPS FOR EACH ROW WHEN (new.MAP_ID IS NULL) BEGIN SELECT MAPS_SEQ.NEXTVAL INTO :new.MAP_ID FROM dual; END; /
Here are three flavors:
- numeric. Simple increasing numeric value, e.g. 1,2,3,....
- GUID. globally univeral identifier, as a
- GUID (string). Same as above, but as a string which might be easier to handle in some languages.
x is the identity column. Substitute
FOO with your table name in each of the examples.
-- numerical identity, e.g. 1,2,3... create table FOO ( x number primary key ); create sequence FOO_seq; create or replace trigger FOO_trg before insert on FOO for each row begin select FOO_seq.nextval into :new.x from dual; end; / -- GUID identity, e.g. 7CFF0C304187716EE040488AA1F9749A -- use the commented out lines if you prefer RAW over VARCHAR2. create table FOO ( x varchar(32) primary key -- string version -- x raw(32) primary key -- raw version ); create or replace trigger FOO_trg before insert on FOO for each row begin select cast(sys_guid() as varchar2(32)) into :new.x from dual; -- string version -- select sys_guid() into :new.x from dual; -- raw version end; /
Oracle 12c introduces these two variants that don't depend on triggers:
create table mytable(id number default mysequence.nextval); create table mytable(id number generated as identity);
The first one uses a sequence in the traditional way; the second manages the value internally.
Assuming you mean a column like the SQL Server identity column?
In Oracle, you use a SEQUENCE to achieve the same functionality. 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
Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column. In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger. From 12c onward, you can create your own Table and define the column that has to be generated as an Identity.
The following article explains how to use it: