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

Best approach: Get the next value from sequence

The nicest approach is getting the NEXTVAL from the SEQUENCE "associated" with the table. Since the sequence is not directly associated to any specific table, we will need to manually refer the corresponding table from the sequence name convention.

The sequence name used on a table, if follow the sequence naming convention, will mention the table name inside its name. Something likes <table_name>_SEQ. You will immediately recognize it the moment you see it.

First, check within Oracle system if there is any sequence "associated" to the table

SELECT * FROM all_sequences
WHERE SEQUENCE_OWNER = '<schema_name>';

will present something like this

Grab that SEQUENCE_NAME and evaluate the NEXTVAL of it in your INSERT query

INSERT INTO workqueue(id, value) VALUES (workqueue_seq.NEXTVAL, 'A new value...')

Additional tip

In case you're unsure if this sequence is actually associated with the table, just quickly compare the LAST_NUMBER of the sequence (meaning the current value) with the maximum id of that table. It's expected that the LAST_NUMBER is greater than or equals to the current maximum id value in the table, as long as the gap is not too suspiciously large.

SELECT LAST_NUMBER
FROM all_sequences
WHERE SEQUENCE_OWNER = '<schema_name>' AND SEQUENCE_NAME = 'workqueue_seq';

SELECT MAX(ID)
FROM workqueue;

Reference: Oracle CURRVAL and NEXTVAL

Alternative approach: Get the current max id from the table

The alternative approach is getting the max value from the table, please refer to Zsolt Sky answer in this same question

Answer 2

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

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

Note : here need to use count(1) in place of max(id) column

It perfectly works for an empty table also.

Answer 3

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.

Answer 4

For completeness, I'll mention that Oracle 12c does support this feature. Also it's supposedly faster than the triggers approach. For example:

CREATE TABLE foo
  (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (
    START WITH 1 NOCACHE ORDER ) NOT NULL ,
    name       VARCHAR2 (50)
  )
  LOGGING ;
ALTER TABLE foo ADD CONSTRAINT foo_PK PRIMARY KEY ( id ) ;

Answer 5

the complete know how, i have included a example of the triggers and sequence

create table temasforo(
idtemasforo NUMBER(5) PRIMARY KEY,
autor       VARCHAR2(50) NOT NULL,
fecha       DATE DEFAULT (sysdate),
asunto      LONG  );

create sequence temasforo_seq
  start with 1
  increment by 1
  nomaxvalue;

create or replace
trigger temasforo_trigger
  before insert on temasforo
  referencing OLD as old NEW as new
  for each row
  begin
      :new.idtemasforo:=temasforo_seq.nextval;
    end;

reference: http://thenullpointerexceptionx.blogspot.mx/2013/06/llaves-primarias-auto-incrementales-en.html

Answer 6

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 7

SQL trigger for automatic date generation in oracle table:

CREATE OR REPLACE TRIGGER name_of_trigger

BEFORE INSERT

ON table_name

REFERENCING NEW AS NEW

FOR EACH ROW

BEGIN

SELECT sysdate INTO :NEW.column_name FROM dual;

END;

/

Answer 8

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.

Answer 9

You can use either SEQUENCE or TRIGGER to increment automatically the value of a given column in your database table however the use of TRIGGERS would be more appropriate. See the following documentation of Oracle that contains major clauses used with triggers with suitable examples.

Use the CREATE TRIGGER statement to create and enable a database trigger, which is:

• A stored PL/SQL block associated with a table, a schema, or the database or

• An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

Oracle Database automatically executes a trigger when specified conditions occur. See.

---

Following is a simple TRIGGER just as an example for you that inserts the primary key value in a specified table based on the maximum value of that column. You can modify the schema name, table name etc and use it. Just give it a try.

/*Create a database trigger that generates automatically primary key values on the CITY table using the max function.*/

CREATE OR REPLACE TRIGGER PROJECT.PK_MAX_TRIGGER_CITY
BEFORE INSERT ON PROJECT.CITY
FOR EACH ROW
DECLARE 
    CNT NUMBER;
    PKV CITY.CITY_ID%TYPE;
    NO NUMBER;
BEGIN
    SELECT COUNT(*)INTO CNT FROM CITY;

    IF CNT=0 THEN
        PKV:='CT0001';
    ELSE
        SELECT 'CT'||LPAD(MAX(TO_NUMBER(SUBSTR(CITY_ID,3,LENGTH(CITY_ID)))+1),4,'0') INTO PKV
        FROM CITY;
    END IF; 
    :NEW.CITY_ID:=PKV;
END;

Would automatically generates values such as CT0001, CT0002, CT0002 and so on and inserts into the given column of the specified table.

Answer 10

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