db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From José Ventura <st.ne...@gmail.com>
Subject Re: Using Identity Colums with or without Sequences derby 10.8.1.2
Date Mon, 01 Oct 2012 13:49:15 GMT
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, <Malte.Kempff@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******
>
>
>

Mime
View raw message