Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  b=ycLUraNPsk8FHFy6/hkMqeNFiflJp+kKy9oCKakXGMQhqJxLTjphuuklBZeRNCYlqATCJRH48XHvEESzNY8jpxJLbV4nVCz/gNL9Ndx1SCS9SO8hWlhJa6YHoJ1wH83w/rNWAWLfYCZuoEvgsLhO5qozQW4P/AD90pQNYlIgpbg=
  ;
Message-ID: <20050222193940.54268.qmail@web53007.mail.yahoo.com>
Date: Tue, 22 Feb 2005 11:39:39 -0800 (PST)
From: Suavi Ali Demir <demir4@yahoo.com>
Subject: Re: identity column causes grief when populating table - suggestions?
To: Derby Discussion <derby-user@db.apache.org>
In-Reply-To: <421B8A3F.2090908@protocool.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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 <trevor@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
> 
>