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: neutral (hermes.apache.org: local policy)
Message-ID: <421B8B54.8080906@Sourcery.Org>
Date: Tue, 22 Feb 2005 11:43:16 -0800
From: Satheesh Bandaram <satheesh@Sourcery.Org>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: identity column causes grief when populating table - suggestions?
References: <421B8A3F.2090908@protocool.com>
In-Reply-To: <421B8A3F.2090908@protocool.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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