db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Rodriguez <crodrig...@gmail.com>
Subject Re: Inserting values in an identity column
Date Tue, 08 Mar 2005 18:09:20 GMT
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
> > >
> > >
> > >
> >
> >
>

Mime
View raw message