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:[email protected]] 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, <[email protected]> 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
