Auto Incrementing Primary Key in ORACLE

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
You will have to do the following in order to achieve an auto incrementing primary key in oracle. First, create a table with a primary key.

CREATE TABLE OwnerSchema.Users
(
UserId NUMBER NOT NULL,
UserName VARCHAR(30) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

COMMIT;

ALTER TABLE OwnerSchema.Users ADD (
CONSTRAINT Users_PK
PRIMARY KEY
(UserId));

COMMIT;

Then create a sequence, this will be used to read the primary key values from.

CREATE SEQUENCE Users_Seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

COMMIT;

Finally, create a trigger to auto increment the primary key using the sequence.

CREATE TRIGGER OwnerSchema.Users_Trigger
BEFORE INSERT ON OwnerSchema.USERS FOR EACH ROW
BEGIN
SELECT Users_Seq.NEXTVAL INTO :NEW.UserId FROM Dual;
END;