How to define a auto increment column using oracle 11g

Question

Ho can i define a integer auto increment with oracle 11g?This is my code with mysql user_id int(6) not null auto_increment primary key how can i have this line with oracle?Because i've already the same database in mysql now i want to build the same structure with oracle

Possible duplicate of [Auto Increment for Oracle](http://stackoverflow.com/questions/9733085/auto-increment-for-oracle) — mathguy, Feb 02 '17 at 01:03
for oracle 12c+ follow this https://stackoverflow.com/a/11296469/10531665 — Susobhan Das, Oct 23 '20 at 08:15

score 1 · Accepted Answer · answered Feb 02 '17 at 03:55

You can achieve this with a sequence.

CREATE SEQUENCE seq_user;

The above will auto increment by 1, and start at 1.
To insert values using this sequence, you can do the following (for example):

INSERT INTO table_name (user_id) VALUES (seq_user.NEXTVAL);

To automate this process, you could reference the sequence in a trigger on the table, that adds this value on an insert automatically:

CREATE OR REPLACE TRIGGER user_trg
BEFORE INSERT ON table_name
FOR EACH ROW

DECLARE

BEGIN
  IF(inserting)
  THEN
    :NEW.USER_ID := seq_user.NEXTVAL;
  END IF;

END;

In oracle this is called a surrogate key. – sandman Feb 03 '17 at 12:15 — sandman, Feb 03 '17 at 12:15

How to define a auto increment column using oracle 11g

1 Answers1