0

I have a table that has an id column but not primary key.They all manually added id's.So what I want to do is insert some values to that table but it requires id.How can i add id column specific values while inserting?

ekcb9
  • 13
  • 5
  • Possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) – Rene Aug 01 '18 at 13:57
  • the table i am working with already EXISTS. I am trying to insert values in that table but it wants me to enter an id.But id column is not primary key.Before me they inserted id numbers manually – ekcb9 Aug 01 '18 at 14:04
  • 1
    "it wants me to enter an id" - what does? Are you using an application or a tool to insert, and what error do you get? Why can't you insert values manually as people have before; or are you trying to change from manually setting them to having them auto-generated? – Alex Poole Aug 01 '18 at 14:58

2 Answers2

0
CREATE SEQUENCE seq$table1
START WITH 10
INCREMENT BY 1
NOCACHE
NOCYCLE;

CREATE OR REPLACE TRIGGER trg$table1
BEFORE INSERT
ON table1
FOR EACH ROW   
REFERENCING NEW AS N OLD AS O
BEGIN
  select seq$table1.nextVal
  into :n.id
  from dual;
END;
/

update table1
set id = seq$table1.nextVal;
commit;
KO63OH
  • 1
  • 2
0

I can suggest one approach, code you can write by yourself with little help of google or let me know if you need a code will edit the answer: 1.create a sequence. 2.write a procedure for the insert which will check if id generated by a sequence exists in the table or not. Use a loop to check If id exists then call sequence.next till you get id which does not exist in the table.

Note: You can also use trigger but that will give mutating table error so if you can handle that error you will have the perfect solution.

Let me know if you need code for the above steps but I would suggest you try first. Thanks

Bhanu Yadav
  • 169
  • 7