You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by rgasch <rg...@gmail.com> on 2013/03/27 01:21:34 UTC
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
beside 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 at Nabble.com.
Re: IDENTITY fields as primary keys: inserts vs. system generated
values
Posted by "Stephan van Loendersloot (LIST)" <st...@republika.nl>.
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 <rg...@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
Re: IDENTITY fields as primary keys: inserts vs. system generated values
Posted by George Sexton <ge...@mhsoftware.com>.
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 <rg...@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
beside 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 at Nabble.com.
Re: IDENTITY fields as primary keys: inserts vs. system generated
values
Posted by "George S." <ge...@mhsoftware.com>.
FWIW, on SQL Server, you have to do a DBCC CHECKIDENT command, and on
PostgreSQL, you do an ALTER SEQUENCE x RESTART WITH command.
On 3/26/13 6:21 PM, rgasch wrote:
> 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
> beside 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 at Nabble.com.
>
--
George Sexton
MH Software, Inc.
303 438-9585
http://www.mhsoftware.com/