Oracle sql adding auto incremented field

Question

I have made a table in oracle which uses auto incremented field thorough sequence.

Here is the sql:

CREATE TABLE Users(
   user_ID   INT          NOT NULL,
   user_name VARCHAR (20)     NOT NULL,
   user_password  VARCHAR (20)              NOT NULL,
   user_role  INT  NOT NULL,

   PRIMARY KEY (user_ID)
);

ALTER TABLE Users 
   ADD FOREIGN KEY (user_role) REFERENCES User_Roles (role_ID);
CREATE SEQUENCE seq_users
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

Now I need to insert the data into the table through a java program, is there any way, I don't have to use the query like this:

Insert into User_Roles values (seq_user_roles.nextval,'system admin');

User Role Table:
CREATE TABLE User_Roles(
       role_ID   INT              NOT NULL,
       role_name VARCHAR (20)     NOT NULL,


       PRIMARY KEY (role_ID)
);


CREATE SEQUENCE seq_user_roles
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

I want to insert the data from a java program and can't specify that name of the sequence.

Create a before insert trigger type so it will populate data into role_id. If you need more explanation i will post some sample code. — Tharunkumar Reddy
– Tharunkumar Reddy, Commented Aug 11, 2015 at 5:54
@Tarun Thanks for the answer and it helped me a lot, is it going to work if don't specify anything for the field role_id when I am doing it through java program, I have tried through and it is not working for me — Haider Sultan
– Haider Sultan, Commented Aug 11, 2015 at 6:02

Tharunkumar Reddy · Accepted Answer · 2015-08-11 06:04:26Z

1

Crete below trigger after sequence creation. So it will populate date into your column. And no need to mention role_id column in you insert statement script.

CREATE OR REPLACE TRIGGER TRG_User_Roles_BRI
       BEFORE INSERT
       ON User_Roles
       REFERENCING NEW AS NEW OLD AS OLD
       FOR EACH ROW
    BEGIN
       :NEW.role_ID   := seq_user_roles.NEXTVAL;
    END;

answered Aug 11, 2015 at 6:04

Tharunkumar Reddy

2,81320 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle sql adding auto incremented field

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related