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 Ma...@de.equens.com on 2012/10/01 15:26:41 UTC
Using Identity Colums with or without Sequences derby 10.8.1.2
Hi,
I'd like to use an identity-column And I am not quite sure how to
receive the used number, if I used default.
Actually I need the particular identity-value for the entries of other
tables, since they are supposed to reference it.
Am I forced to give those identities for my own like with a
sequence-number, or can I somwhow get it by some "magic" statement?
--------------------------------
here my particular case:
CREATE TABLE "APP"."INPUTFILES"
(
INPUTFILE_ID int generated always as
identity
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "APP"."OUTPUTFILES"
(
OUTPUTFILE_ID int generated always as
identity
,INPUTFILE_ID int
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY
KEY ("INPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID"
PRIMARY KEY ("OUTPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN
KEY ("INPUTFILE_ID") REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON
DELETE CASCADE ON UPDATE RESTRICT;
---------------
Am I right that I need to use generated by default as identity instead
of int generated always as identity
And that there is no way to get the identity value if generated by
derby?
Malte Kempff
AW: Using Identity Columns with or without Sequences derby 10.8.1.2
Posted by Ma...@de.equens.com.
Hi John,
Thanks a lot for your code example that helps me a lot saveing some time
:-)
Malte
________________________________
Von: John I. Moore, Jr. [mailto:softmoore@att.net]
Gesendet: Montag, 1. Oktober 2012 15:54
An: 'Derby Discussion'
Betreff: RE: Using Identity Columns with or without Sequences derby
10.8.1.2
Matt,
I use generated IDs a lot in my applications. My implementation looks
something like the following:
create table X
(
x_id int generated always as identity (start with 1000),
...
constraint x_pk primary key (x_id)
);
In order to get access to the generated ID, my code looks something like
the following:
ConnectionFactory connFactory = ConnectionFactory.getInstance();
conn = connFactory.getConnection();
stmt = conn.prepareStatement(insertQuery,
PreparedStatement.RETURN_GENERATED_KEYS);
... // set prepared statement parameters
stmt.executeUpdate();
// get id from the database
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next())
{
int xId = rs.getInt(1);
...
}
_________________________________________
John I. Moore, Jr.
SoftMoore Consulting
email: jmoore@softmoore.com
web: www.softmoore.com
cell: 843-906-7887
From: Malte.Kempff@de.equens.com [mailto:Malte.Kempff@de.equens.com]
Sent: Monday, October 01, 2012 9:27 AM
To: derby-user@db.apache.org
Subject: Using Identity Columns with or without Sequences derby 10.8.1.2
Hi,
I'd like to use an identity-column And I am not quite sure how to
receive the used number, if I used default.
Actually I need the particular identity-value for the entries of other
tables, since they are supposed to reference it.
Am I forced to give those identities for my own like with a
sequence-number, or can I somwhow get it by some "magic" statement?
--------------------------------
here my particular case:
CREATE TABLE "APP"."INPUTFILES"
(
INPUTFILE_ID int generated always as
identity
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "APP"."OUTPUTFILES"
(
OUTPUTFILE_ID int generated always as
identity
,INPUTFILE_ID int
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY
KEY ("INPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID"
PRIMARY KEY ("OUTPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN
KEY ("INPUTFILE_ID") REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON
DELETE CASCADE ON UPDATE RESTRICT;
---------------
Am I right that I need to use generated by default as identity instead
of int generated always as identity
And that there is no way to get the identity value if generated by
derby?
Malte Kempff
RE: Using Identity Columns with or without Sequences derby 10.8.1.2
Posted by "John I. Moore, Jr." <so...@att.net>.
Matt,
I use generated IDs a lot in my applications. My implementation looks something like the following:
create table X
(
x_id int generated always as identity (start with 1000),
...
constraint x_pk primary key (x_id)
);
In order to get access to the generated ID, my code looks something like the following:
ConnectionFactory connFactory = ConnectionFactory.getInstance();
conn = connFactory.getConnection();
stmt = conn.prepareStatement(insertQuery, PreparedStatement.RETURN_GENERATED_KEYS);
... // set prepared statement parameters
stmt.executeUpdate();
// get id from the database
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next())
{
int xId = rs.getInt(1);
...
}
_________________________________________
John I. Moore, Jr.
SoftMoore Consulting
email: jmoore@softmoore.com
web: www.softmoore.com
cell: 843-906-7887
From: Malte.Kempff@de.equens.com [mailto:Malte.Kempff@de.equens.com]
Sent: Monday, October 01, 2012 9:27 AM
To: derby-user@db.apache.org
Subject: Using Identity Columns with or without Sequences derby 10.8.1.2
Hi,
I'd like to use an identity-column And I am not quite sure how to receive the used number, if I used
default.
Actually I need the particular identity-value for the entries of other tables, since they are
supposed to reference it.
Am I forced to give those identities for my own like with a sequence-number, or can I somwhow get it
by some "magic" statement?
--------------------------------
here my particular case:
CREATE TABLE "APP"."INPUTFILES"
(
INPUTFILE_ID int generated always as identity
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "APP"."OUTPUTFILES"
(
OUTPUTFILE_ID int generated always as identity
,INPUTFILE_ID int
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY KEY ("INPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID" PRIMARY KEY ("OUTPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN KEY ("INPUTFILE_ID")
REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON DELETE CASCADE ON UPDATE RESTRICT;
---------------
Am I right that I need to use generated by default as identity instead of int generated always as
identity
And that there is no way to get the identity value if generated by derby?
Malte Kempff
AW: Using Identity Colums with or without Sequences derby 10.8.1.2
Posted by Ma...@de.equens.com.
Hi José,
Thanks a lot for the hints,
by the way are those identity-values cycled, so it does not come to crash if there a is a "roll over", unless there is a defined constrained that says not unique?
Malte
________________________________
Von: José Ventura [mailto:st.never@gmail.com]
Gesendet: Montag, 1. Oktober 2012 15:49
An: Derby Discussion
Betreff: Re: Using Identity Colums with or without Sequences derby 10.8.1.2
I believe this documentation page answers your question about how to retrieve the generated values:
http://db.apache.org/derby/docs/10.7/ref/crefjavstateautogen.html
In your case, the INSERT statement that creates a record in INPUTFILES will return a ResultSet which contains the generated value. You can then use this value in subsequent INSERT statements to create child records in table OUTPUTFILES.
Your next question, about the difference between GENERATED ALWAYS and GENERATED BY DEFAULT is explained here:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html
Basically, GENERATED BY DEFAULT will only generate a value if you don't explicitly provide one. GENERATED ALWAYS will simply not allow you to specify the value, and will always generate one.
Hope that helps...
On Mon, Oct 1, 2012 at 10:26 AM, <Ma...@de.equens.com> wrote:
Hi,
I'd like to use an identity-column And I am not quite sure how to receive the used number, if I used default.
Actually I need the particular identity-value for the entries of other tables, since they are supposed to reference it.
Am I forced to give those identities for my own like with a sequence-number, or can I somwhow get it by some "magic" statement?
--------------------------------
here my particular case:
CREATE TABLE "APP"."INPUTFILES"
(
INPUTFILE_ID int generated always as identity
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "APP"."OUTPUTFILES"
(
OUTPUTFILE_ID int generated always as identity
,INPUTFILE_ID int
,"NAME" VARCHAR(512) NOT NULL
,"TMPST_IN" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY KEY ("INPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID" PRIMARY KEY ("OUTPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN KEY ("INPUTFILE_ID") REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON DELETE CASCADE ON UPDATE RESTRICT;
---------------
Am I right that I need to use generated by default as identity instead of int generated always as identity
And that there is no way to get the identity value if generated by derby?
Malte Kempff
Re: Using Identity Colums with or without Sequences derby 10.8.1.2
Posted by José Ventura <st...@gmail.com>.
I believe this documentation page answers your question about how to
retrieve the generated values:
http://db.apache.org/derby/docs/10.7/ref/crefjavstateautogen.html
In your case, the INSERT statement that creates a record in INPUTFILES will
return a ResultSet which contains the generated value. You can then use
this value in subsequent INSERT statements to create child records in table
OUTPUTFILES.
Your next question, about the difference between GENERATED ALWAYS and
GENERATED BY DEFAULT is explained here:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html
Basically, GENERATED BY DEFAULT will only generate a value if you don't
explicitly provide one. GENERATED ALWAYS will simply not allow you to
specify the value, and will always generate one.
Hope that helps...
On Mon, Oct 1, 2012 at 10:26 AM, <Ma...@de.equens.com> wrote:
> **
> Hi,
> I'd like to use an identity-column And I am not quite sure how to receive
> the used number, if I used default.
> Actually I need the particular identity-value for the entries of other
> tables, since they are supposed to reference it.
>
> Am I forced to give those identities for my own like with a
> sequence-number, or can I somwhow get it by some "magic" statement?
> --------------------------------
> here my particular case:
> CREATE TABLE "APP"."INPUTFILES"
> (
> INPUTFILE_ID int generated always as
> identity
> ,"NAME" VARCHAR(512) NOT NULL
> ,"TMPST_IN" TIMESTAMP NOT NULL
> DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE TABLE "APP"."OUTPUTFILES"
> (
> OUTPUTFILE_ID int generated always as
> identity
> ,INPUTFILE_ID int
> ,"NAME" VARCHAR(512) NOT NULL
> ,"TMPST_IN" TIMESTAMP NOT NULL
> DEFAULT CURRENT_TIMESTAMP
> );
>
>
> ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY
> KEY ("INPUTFILE_ID");
>
> ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID"
> PRIMARY KEY ("OUTPUTFILE_ID");
>
> ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN
> KEY ("INPUTFILE_ID") REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON
> DELETE CASCADE ON UPDATE RESTRICT;
>
> ---------------
>
> Am I right that I need to use generated by default as identity instead of int
> generated always as identity
> And that there is no way to get the identity value if generated by derby?
>
> Malte Kempff******
>
>
>