CODEWITHSUNDEEP
sqloracle-databaseinsert-into
JDC
JDC

Reputation: 4385

Insert Into Oracle Table with single, autoincrement Column

Imagine the following (fictional) situation:

You have a table with only one column id that is the primary key, autoincremented by using a typical sequence + trigger combination.

How would you create a new row there as you have to specify the values keyword for the insert query?

INSERT INTO table () VALUES () is not valid as far as I understood.

ATTENTION:

This is not a discussion about the sense of such a table! It is out of pure technical interest.

Upvotes: 0

Views: 924

Answers (2)

user330315
user330315

Reputation:

In any current Oracle version (12.1, 12.2, 18) I would not use a trigger but an identity column - then use the default keyword during insert:

create table x (id integer generated by default as identity);
insert into x (id) values (default);

Upvotes: 3

PKey
PKey

Reputation: 3841

How about 

INSERT INTO theTable (id) VALUES (null);

and your before insert trigger would be like: 

if  :NEW.id is NULL Then  
   SELECT id_sequence.NEXTVAL INTO :NEW.id FROM dual; 
end if;

Upvotes: 2

Related Questions