Re: identity column causes grief when populating table - suggestions?

22 Feb 2005 21:57:01 -0000

I agree Derby could use generated by DEFAULT option. But since it is not there currently, omitting the column in the insert list could help where possible.

Satheesh

Suavi Ali Demir wrote:
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 <[EMAIL PROTECTED]> 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

Reply via email to