Search code examples
sqloracleplsqloracle10g

Oracle - Insert New Row with Auto Incremental ID

I have a workqueue table that has a workid column. The workID column has values that increment automatically. Is there a way I can run a query in the backend to insert a new row and have the workID column increment automatically?
When I try to insert a null, it throws error ORA01400 - Cannot insert null into workid.

insert into WORKQUEUE  (facilitycode,workaction,description) values ('J', 'II',    'TESTVALUES')

What I have tried so far - I tried to look at the table details and didn't see any auto-increment. The table script is as follow

"WORKID" NUMBER NOT NULL ENABLE,

Database: Oracle 10g

Screenshot of some existing data. enter image description here

ANSWER:

I have to thank each and everyone for the help. Today was a great learning experience and without your support, I couldn't have done. Bottom line is, I was trying to insert a row into a table that already has sequences and triggers. All I had to do was find the right sequence, for my question, and call that sequence into my query.

The links you all provided me helped me look these sequences up and find the one that is for this workid column. Thanks to you all, I gave everyone a thumbs up, I am able to tackle another dragon today and help patient care take a step forward!"

Solution

  • To get an auto increment number you need to use a sequence in Oracle. (See here and here).

    CREATE SEQUENCE my_seq;

SELECT my_seq.NEXTVAL FROM DUAL; -- to get the next value

-- use in a trigger for your table demo
CREATE OR REPLACE TRIGGER demo_increment 
BEFORE INSERT ON demo
FOR EACH ROW

BEGIN
  SELECT my_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/