Oracle - Insert New Row with Auto Incremental ID

Question

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.

---

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!"

Answer 1

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; / 

Answer 2

This is a simple way to do it without any triggers or sequences:

insert into WORKQUEUE (ID, facilitycode, workaction, description)   values ((select max(ID)+1 from WORKQUEUE), 'J', 'II', 'TESTVALUES') 

It worked for me but would not work with an empty table, I guess.