Inserting nothing into primary auto increment key in Derby database

Question

I created table in Derby database that looks something like that:

create table "DATABASE".SOMETABLE (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) CONSTRAINT PK PRIMARY KEY,
                                   SOMETHING VARCHAR(50) not null)

Now I would like to be able to insert into that table without specifically listing all the columns I want to insert into. So I would like to do something like this:

insert into sometable values ('','something')

I don't want to insert anything into id column but I don't want to be forced to always list names of all the columns I want to insert into. However if I execute this code I get error for trying to modify auto increment primary key. In MySQL and many other database systems I could just do this:

insert into sometable values ('','something')

and it would work but Derby gives me an error. I tried putting NULL instead of '' but I just get the same error. Is there any way to not insert anything into the primary key column with auto increment (without specifically listing all the other columns) in Derby database?

Thanks in advance for any answers.

"In MySQL and many other database systems I could just do this:" - no. Only MySQL allows that. Standard SQL won't allow you to store a string value into an integer column. And a column defined as generated always can't be populated like that to beg with. You would have to at least specify overriding system values to avoid generating a new value, but then you would also have to provide a valid (and unique) integer, not a string. — a_horse_with_no_name

Fidel Fidel · Accepted Answer · 2016-03-08T12:52:32

Use:

insert into sometable values (default, 'something')

The default keyword is documented here

Inserting nothing into primary auto increment key in Derby database

2 Answers