CODEWITHSUNDEEP
sqloracle-databaseauto-increment
GigaPr
GigaPr

Reputation: 5386

Autoincrement Primary key in Oracle database

I would like to achieve an identity or auto-incrementing value in a column ala SQL Server:

CREATE TABLE RollingStock
( 
      Id NUMBER IDENTITY(1,1),
      Name Varchar2(80) NOT NULL      
);

How can this be done?

Upvotes: 6

Views: 9239

Answers (2)

Matthew Watson
Matthew Watson

Reputation: 14233

As Orbman says, the standard way to do it is with a sequence. What most people also do is couple this with an insert trigger. So, when a row is inserted without an ID, the trigger fires to fill out the ID for you from the sequence.

CREATE SEQUENCE SEQ_ROLLINGSTOCK_ID START WITH 1 INCREMENT BY 1 NOCYCLE;

CREATE OR REPLACE TRIGGER BI_ROLLINGSTOCK
BEFORE INSERT ON ROLLINGSTOCK
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
 WHEN (NEW.ID IS NULL)
BEGIN
  select SEQ_ROLLINGSTOCK_ID.NEXTVAL
   INTO :NEW.ID from dual;
END;

This is one of the few cases where it makes sense to use a trigger in Oracle.

Upvotes: 10

anon
anon

Reputation:

If you really don't care what the primary key holds, you can use a RAW type for the primary key column which holds a system-generated guid in binary form.

CREATE TABLE RollingStock 
(  
  ID RAW(16) DEFAULT SYS_GUID() PRIMARY KEY, 
  NAME VARCHAR2(80 CHAR) NOT NULL       
);

Upvotes: 4

Related Questions