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)
Date: Mon, 07 Mar 2005 15:38:32 -0800
From: Trevor Squires <trevor@protocool.com>
Subject: Re: Inserting values in an identity column
In-reply-to: <ce6cc8cf05030712495754d2e9@mail.gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <422CE5F8.1090806@protocool.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <ce6cc8cf05030712495754d2e9@mail.gmail.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)

Hi,

I asked this very question last week (or so) for pretty much the same 
reason.

It doesn't appear that you can alter the table to make it an identity 
column after creation (which was my first idea).  I was also unable to 
find any way to switch off the identity feature temporarily either.

One person suggested that derby ought to support something like 
"generated by default as identity" which makes sense - although I'd 
rather be able to just switch on and off the current behavior for 
loading of dumps and pre-population.

In order to get my project out the door I ended up converting the logic 
to update/select rows in an "identities" table that I manage myself.

HTH,
Trevor

Christian Rodriguez wrote:
> Hi everyone,
> 
> I am trying to import data from a mysql dump file. Basically, I need
> to insert values in tables. Some of the tables in mysql include
> auto_increment columns, that I have translated to "generate always as
> identity" in derby. Now... the mysql dump file has values in those
> columns, and I get an error when trying to insert a value into an
> IDENTITY column in derby. How can I do this?
> 
> I have a couple of ideas, but none work:
> 
> 1. I could define the column as a regular column (not identity) and
> after the values have been populated I could try to ALTER the column
> in the table to make it an identity column, but this last part doesnt
> seem posible.
> 2. Is there another way to implement "autoincrement" in derby?
> 3. Is it posible to disable the "identity" feature until all values
> have been populated?
> 
> Any other ideas?
> 
> Of course, I could let derby generate values for that identity column
> and then map the original values with those, and touch all the
> references in the other tables and convert them to the new identity
> values, but that doesnt seem like a lot of work... and Im getting the
> feeling there should be a more reasonable way to import "dumps" from
> other databases...
> 
> Thanks for any help you can give me,
> Xtian