Menu
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!"
617
You can double click the name of the column or click on the 'Properties' button. Column Properties dialog box appears. Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.
When you define a column in MySQL, you can specify a parameter called AUTO_INCREMENT. Then, whenever a new value is inserted into this table, the value put into this column is 1 higher than the last value. But, Oracle does not have an AUTO_INCREMENT feature.
How you create an IDENTITY field affects what happens when you INSERT values. You cannot change the IDENTITY value of a column that is a primary key. Copy CREATE Table Test_SGSqlInsert2( id INTEGER, name STRING, deptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1), PRIMARY KEY(id));
Right click on the table and select "Edit". In "Edit" Table window, select "columns", and then select your PK column. Go to Identity Column tab and select "Generated as Identity" as Type, put 1 in both start with and increment field. This will make this column auto increment.
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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
