db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suavi Ali Demir <dem...@yahoo.com>
Subject Re: identity column causes grief when populating table - suggestions?
Date Tue, 22 Feb 2005 20:43:52 GMT
The next import may be a table with foreign keys to
the first one. Sometimes (most of the time actually)
it is desireable to keep the original values in an
identity column. 

Having to use "generated always" blocks many solutions
including import/export, replication/sync where key
space could be partitioned between master slave to be
able to replicate identity columns back and forth.
(Want to have 2 databases where in one database i
insert values 0-2 billion and in another i insert 2
billion to 4 billion and i want these rows to be
usable in both databases). "generated by default" may
help greatly to make this possible for auto int
columns.
Regards,
Ali

--- Satheesh Bandaram <satheesh@Sourcery.Org> wrote:

> You are trying to insert into a column that is
> declared as identity.
> Database automatically generates a unique number for
> identity columns,
> so just leave out ID column in your INSERT
> statement, like:
> 
>     insert into sample (DESCRIPTION, QUANTITY)
> values ('blah', 1.1);
> 
> That should fix it...
> 
> Satheesh
> 
> Trevor Squires wrote:
> 
> > 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