db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernd Ruehlicke" <BRuehli...@lgc.com>
Subject RE: identity column causes grief when populating table - suggestions?
Date Tue, 22 Feb 2005 19:41:38 GMT
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:trevor@protocool.com] 
> Sent: Tuesday, February 22, 2005 1:39 PM
> To: derby-user@db.apache.org
> 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
> 
> 

Mime
View raw message