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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Oracle - Insert New Row with Auto Incremental ID

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g