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
<[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
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
