The idea with th eidentiy colum is that you do not insert any value in
that particular column.
So in your eample
insert into sample (DESCRIPTION, QUANTITY) values ('blah', 1.1);
is the way to do it. You can always ask the system what the latest ID
used was via
VALUES IDENTITY_VAL_LOCAL()
B-)
PS: I have added a JIRA to get a global sequence generator for Derby -
you may vote on that as well ...
> -----Original Message-----
> From: Trevor Squires [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 22, 2005 1:39 PM
> To: [email protected]
> Subject: identity column causes grief when populating table -
> suggestions?
>
> Hi,
>
> I've been googling all morning and can't find info to solve
> this problem (using derby of course), I hope someone here can help...
>
> As I write my application I have a script which blats and
> recreates my database and then populates the tables with test data.
>
> However, many of my tables have identity columns and it's
> causing the following error when I insert the data:
>
> 11:06:26 [INSERT - 0 row(s), 0.016 secs] [Error Code: 30000, SQL
> State: 42Z23] Attempt to modify an identity column 'ID'.
>
> Here's my table:
>
> create table sample (
> id int not null generated always as identity,
> description varchar(128) not null,
> quantity double not null,
> constraint product_pk primary key (id) );
>
> Here's my insert statement
>
> insert into sample (ID, DESCRIPTION, QUANTITY) values (1,
> 'blah', 1.1);
>
> I've tried to create the table without "generated always as identity"
> and then altering the table after the insert(s).
>
> Unfortunately I can't get the syntax right and I can't figure
> out if there's a way to turn off identity generation temporarily.
>
> Does anyone have any suggestions like pointer to the right
> way to do this or a relatively painless workaround?
>
> Thanks for listening,
> Trevor
>
>
