You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Trevor Squires <tr...@protocool.com> on 2005/02/22 20:38:39 UTC
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
Re: identity column causes grief when populating table - suggestions?
Posted by Suavi Ali Demir <de...@yahoo.com>.
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 <sa...@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
> >
> >
> >
>
>
Re: identity column causes grief when populating table - suggestions?
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
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
>
>
>
Re: identity column causes grief when populating table - suggestions?
Posted by Trevor Squires <tr...@protocool.com>.
Thanks Ali,
the docs I've found only show support for "generated always as"
tinkering with variations on that syntax doesn't seem to work...
I agree that it would solve my problem - if it existed today.
Thanks,
Trevor
Suavi Ali Demir wrote:
> Will derby support "default" keyword as in:
>
> create table a(
> id int not null generated by DEFAULT as identity,
> ...
>
> or something like that? That would solve these
> problems as it would only generate values if we do not
> specify the value.
>
> Regards,
> Ali
>
>
>
> --- Trevor Squires <tr...@protocool.com> 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
>>
>>
>
>
Re: identity column causes grief when populating table - suggestions?
Posted by Suavi Ali Demir <de...@yahoo.com>.
Will derby support "default" keyword as in:
create table a(
id int not null generated by DEFAULT as identity,
...
or something like that? That would solve these
problems as it would only generate values if we do not
specify the value.
Regards,
Ali
--- Trevor Squires <tr...@protocool.com> 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
>
>