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 includes SPF record at
 spf.trusted-forwarder.org)
Mime-Version: 1.0 (Apple Message framework v619.2)
In-Reply-To: <ce6cc8cf050308100931e51a2b@mail.gmail.com>
References: 
 <1B37BEFB3456F1428E01932AFA147822E1A58D@HOUEXCH902.landmark.lgc.com>
 <ce6cc8cf050308100931e51a2b@mail.gmail.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <9669f4717161a8ff7ca1407fd7b987d7@apache.org>
Content-Transfer-Encoding: 7bit
From: David Jencks <djencks@apache.org>
Subject: Re: Inserting values in an identity column
Date: Tue, 8 Mar 2005 10:47:43 -0800
To: "Derby Discussion" <derby-user@db.apache.org>

While Bernd suggested an additional use for sequences, namely db-wide 
unique identifiers, which may or may not be useful in various 
situations, it is obviously possible to use sequences + triggers to 
replace identity columns.  Using a plain column filled in by an insert 
trigger completely sidesteps the problems of identity columns, since 
you'd just add the trigger after the data load was complete, or make 
the trigger fire  only when no pk value was supplied. After the load is 
complete you set the sequence values to the appropriate maximum of 
existing data.  Even I who am no dba have done this when migrating a 
database from infomix to oracle.

thanks
david jencks

On Mar 8, 2005, at 10:09 AM, Christian Rodriguez wrote:

> Bernd, your solution doesnt solve my problem AT ALL. It doesnt solve
> the problem of migration from other databases to Derby, which seems to
> be a very important feature.
>
> Most databases generate "dumps", either as comma separated files or as
> "inserts". Those inserts contain values, even for identity or
> "autoincrement" columns. Any reasonable DB system should be able to
> take those inserts and restore the values in the database as a way to
> migrate from one RDBMS to another.
>
> The "sequence" feature is a nice feature, but it wouldnt solve this
> problem at all: you would have to map every value in the "dump" file
> with the value given by the "sequence" and 'translate" all the
> references, i.e. a big mess.
>
> You are trying to solve the problem of "unique identifiers throughout
> the database", Im talking about "inserting specific values in identity
> columns". They may be related, but they are not the same problem.
>
> I just filed a JIRA. Thanks.
> Xtian
>
>
> On Tue, 8 Mar 2005 11:55:45 -0600, Bernd Ruehlicke 
> <BRuehlicke@lgc.com> wrote:
>> Yes, but (in my personal opinion) it would be even better if we had a
>> global sequence generator like Oracle, MS SQL etc
>>
>> See also http://issues.apache.org/jira/browse/DERBY-103
>>
>> Than a user does not need to worry about this and just create a usual
>> colum which will support altering. The price is of course that you 
>> have
>> to insert the value yourselfe into this coulm but you may than use a
>> function like  "mySequence.nextval". This makes also sure that every
>> single row in your database (if each table has an identity) has a 
>> unique
>> key - in all of the database. This becomse pretty handy if a table 
>> holds
>> the key and name of other table(s) - generically. Like
>>
>> MyTable
>> table_id       VARCHAR
>> ...
>> Source_ID      VARCHAR   (if a text string is used as sequence key)
>> Source_Table   VARCHAR  (name of source table)
>> ...
>> ...
>>
>> If you only had column identifiers you will have scenarious where
>> "Source_ID" is the same for 2 differnt tables ! That is not possible
>> when using a global sequence.
>>
>> ... anyhow ... just some words for discussion.
>>
>> B-)
>>
>>
>>> -----Original Message-----
>>> From: Kathey Marsden [mailto:kmarsdenderby@sbcglobal.net]
>>> Sent: Tuesday, March 08, 2005 11:38 AM
>>> To: Derby Discussion; Derby Development
>>> Subject: Re: Inserting values in an identity column
>>>
>>> Christian,
>>>
>>> Could you please file a Jira entry for this?  I am hoping
>>> there someone in the Derby development community that has
>>> time to take up this issue and propose and implement a
>>> solution.  It seems to me that this is a serious hindrance to
>>> migration to Derby that would be good to resolve sooner
>>> rather than later.
>>>
>>> You can file it as an "Improvement"  in Jira at:
>>> http://issues.apache.org/jira/secure/CreateIssue!default.jspa
>>>
>>> Thanks
>>>
>>> Kathey
>>>
>>> Trevor Squires wrote:
>>>
>>>> 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
>>>>
>>>>
>>>>
>>>
>>>
>>
>