Oracle - Insert New Row with Auto Incremental ID
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;
/
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.
ELXAN@DB1> create table cedvel(id integer,ad varchar2(15)); Table created. ELXAN@DB1> alter table cedvel add constraint pk_ad primary key(id); Table altered. ELXAN@DB1> create sequence test_seq start with 1 increment by 1; Sequence created. ELXAN@DB1> create or replace trigger ad_insert before insert on cedvel REFERENCING NEW AS NEW OLD AS OLD for each row begin select test_seq.nextval into :new.id from dual; end; / 2 3 4 5 6 7 8 Trigger created. ELXAN@DB1> insert into cedvel (ad) values ('nese'); 1 row created.
There is no built-in auto_increment in Oracle.
You need to use sequences
and triggers
.
Read here how to do it right. (Step-by-step how-to for "Creating auto-increment columns in Oracle")