db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Malte.Kem...@de.equens.com>
Subject AW: Using Identity Colums with or without Sequences derby 10.8.1.2
Date Mon, 01 Oct 2012 14:03:10 GMT
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, <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