db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephan van Loendersloot (LIST)" <step...@republika.nl>
Subject Re: IDENTITY fields as primary keys: inserts vs. system generated values
Date Wed, 27 Mar 2013 04:31:13 GMT


On 27-03-13 04:10, George Sexton wrote:

> There is a command to update the identity value after a load. I don't
> recall it exactly, and I am away from my computer. I will mail the
> solution in the morning.
>
> Sent from my Kindle Fire
>
>
>
> ------------------------------------------------------------------------
> *From:* rgasch <rgasch@gmail.com>
> *Sent:* Tue Mar 26 18:21:34 MDT 2013
> *To:* derby-user@db.apache.org
> *Subject:* IDENTITY fields as primary keys: inserts vs. system generated
> values
>
> I'm in the process of porting an application to derby and I've hit a major
> stumbling block in the sense that we're using a SQL script to populate our
> database. Since we have columns declared as
>    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY
> and
>    PRIMARY KEY (id)
> we're hitting the situation that in our application inserts fail since the
> system generated IDs are already taken by the IDs claimed by our SQL load.
>
> I've found the following thread related to this
>
> http://apache-database.10148.n7.nabble.com/quot-generated-by-default-quot-question-tt98475.html
>
> I understand the arguments made in the thread that this is not a bug but
> rather a feature (I'm not saying I'm agreeing with this notion but that's
> besi
>   de the
> point). But since this thread is from 2006, I was wondering if in
> the meantime some sort of solution has been added to derby to gracefully
> avoid generating already used sequence numbers?
>
> Can someone who's in the know confirm or deny this?
>
> Greetings/Thanks
> Robert
>
>
>
>
> --
> View this message in context:http://apache-database.10148.n7.nabble.com/IDENTITY-fields-as-primary-keys-inserts-vs-system-generated-values-tp128400.html
> Sent from the Apache Derby Users mailing list archive atNabble.com  <http://Nabble.com>.
>

Hi,

ALTER TABLE mytable ALTER COLUMN my_identity_column RESTART WITH 100

So you can use:

ALTER TABLE mytable ALTER COLUMN my_identity_colum RESTART WITH
  SELECT MAX(my_identity_column) + 1 FROM my_table

Reference:

https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/ref/rrefsqlj81859.html

Kind regards,

   --Stephan van Loendersloot

Mime
View raw message