db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul J. Lucas" <pauljlu...@mac.com>
Subject Atomicity of using IDENTITY_VAL_LOCAL()
Date Fri, 13 May 2005 04:12:33 GMT
 	If I have a tables like:

         	CREATE TABLE location (
         	    location_id    INT NOT NULL PRIMARY KEY
         	                   GENERATED ALWAYS AS IDENTITY,
         	    path           VARCHAR(255) NOT NULL UNIQUE
         	);

         	CREATE TABLE image (
         	    image_id       INT NOT NULL PRIMARY KEY
         	                   GENERATED ALWAYS AS IDENTITY,
         	    file_name      VARCHAR(64) NOT NULL,
         	    location_id    INT REFERENCES location,
         	    date_time      TIMESTAMP NOT NULL
         	);

 	I want to be able to insert a new location, obtain the
 	auto-generated location_id, and use that in an insert of a new
 	image.

 	If I do the fisrt insert, then:

 		SELECT IDENTITY_VAL_LOCAL() FROM location;

 	then use that value for the second insert image.location_id,
 	will I be guaranteed to get the right value for location_id,
 	i.e., the one for the first insert and not some other insert
 	that may have happened in between my firt insert and the
 	SELECT?

 	Pharsed another way, are the operations of:

 		INSERT INTO location ... ;
 		SELECT IDENTITY_VAL_LOCAL() FROM location;
 		INSERT INTO image ... ;

 	atomic so that the value I use in the second insert will be the
 	correct one?

 	If not, how do I do what I want?

 	When I was doing this sort of thing using Oracle, Oracle has
 	"sequences" that can be used for this purpose.  I suppose that
 	for Derby I could have a seperate table that's just:

         	CREATE TABLE location_id (
         	    next_id INT NOT NULL PRIMARY KEY
         	            GENERATED ALWAYS AS IDENTITY,
 		    junk    INT

         	);

 	then, before I do my first insert, do:

 		INSERT INTO location_id (junk) VALUES (0);
 		SELECT IDENTITY_VAL_LOCAL() FROM location_id;

 	then use *that* location_id for both of my inserts.  But is this
 	necessary?

 	Thanks.

 	- Paul

Mime
View raw message