Oracle: how to create an identity column?

Question

What is the magical incantation to create an identity column in Oracle?

Answer 1

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

Answer 2

Here are three flavors:

1. numeric. Simple increasing numeric value, e.g. 1,2,3,....
2. GUID. globally univeral identifier, as a RAW datatype.
3. 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;
/