CODEWITHSUNDEEP
oracle-database
Sperick
Sperick

Reputation: 2761

Using sequential values for the primary key in an INSERT query

How can I write an insert query for an Oracle database which has a sequential primary key so that the insert statement automatically takes the next number in the sequence?

INSERT INTO LD_USER_ROLE(USER_ROLE_ID,INS_USER,INS_DATE, USERNAME) 
VALUES (100, 'sp22',to_date('2003/05/03 21:02:44','yyyy/mm/dd hh24:mi:ss'),'JOHN BARRY', )

In the above statement I have hardcoded the value of 100 for the key 'USER_ROLE_ID' but I'd like to alter this as explained in the first paragraph.

Upvotes: 2

Views: 7336

Answers (2)

jexact
jexact

Reputation: 541

Why don't you just create a trigger for your sequence like this:

Sequence:

CREATE SEQUENCE LD_USER_ROLE_SEQ
    INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE NOORDER;

Trigger:

CREATE TRIGGER LD_USER_ROLE_INSERT BEFORE INSERT ON LD_USER_ROLE
    REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
    SELECT LD_USER_ROLE_SEQ.NEXTVAL INTO :NEW.USER_ROLE_ID FROM DUAL;
END;

The trigger will automatically get the next value/id on every insert (like auto_increment in mysql).

Upvotes: 5

user330315
user330315

Reputation:

Apart from using a trigger, you can use a sequence directly in the insert statement:

CREATE SEQUENCE LD_USER_ROLE_SEQ;

INSERT INTO LD_USER_ROLE
   (USER_ROLE_ID,INS_USER,INS_DATE, USERNAME) 
VALUES 
   (ld_user_role_seq.nextval, 'sp22',to_date('2003/05/03 21:02:44','yyyy/mm/dd hh24:mi:ss'),'JOHN BARRY', )

Upvotes: 4

Related Questions