Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: softfail (nike.apache.org: transitioning domain of
 sylvain@chicoree.fr does not designate 80.12.242.1 as permitted sender)
Message-ID: <4B77EDED.8050201@chicoree.fr>
Date: Sun, 14 Feb 2010 13:34:53 +0100
From: Sylvain Leroux <sylvain@chicoree.fr>
User-Agent: Thunderbird 2.0.0.23 (Macintosh/20090812)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: self referencing column
References: <4B669D03.5070202@yahoo.de> <x1oxvdegpkv5.fsf@sun.com>
 <4B67F584.90007@yahoo.de>
In-Reply-To: <4B67F584.90007@yahoo.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi Joe,

Maybe this is a "too late" answer, but I think one reason you experiencing some 
problems here, is that you are using a single table to represent a N-to-N 
relationship.

Why not instead using two tables:
- one for the states
- one for the transitions

The "state" table could have an "id" GENERATED ALWAYS AS IDENTITY,
and the states on your "transition" table could have the required REFERENCES clause.

Something like that:

CREATE TABLE state (
  id int GENERATED ALWAYS AS IDENTITY
  -- possibly other state-related attributes here
);

CREATE TABLE transition (
  start int NOT NULL REFERENCES state(ID),
  end int NOT NULL REFERENCES state(ID),
  -- possibly other transition-related attributes here
)

Inserting a new self-referencing state would become:
INSERT INTO state() VALUES();
INSERT INTO transition (start, end) VALUES (IDENTITY_VAL_LOCAL(), 
IDENTITY_VAL_LOCAL());

According to the documentation [1], since IDENTITY_VAL_LOCAL returns the last 
generated identity for a /connection/, I think this does not require a specific 
isolation level to deal with possible concurrent access to you DB.

[1] http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html

Sylvain

joe a �crit :
> 
> Hi,
>> Perhaps another way to work around this is to use GENERATED BY DEFAULT as
>> IDENTITY.
>>
>>    
> 
> But how?
> I dont want the user to define the id.
> 
> Ok i can use a helper table which holds the id
> create table state_id
> (
>     ID int not null
> )
> insert into state_id values (0)
> 
> and doing the follwing two commands for every state insert:
> update state_id set id = ((select id from state_id) +1)
> insert into state values ((select id from state_id),  (select id from 
> state_id))
> 
> Looks quite ugly for me.
> 
> 
> 


-- 
sylvain@chicoree.fr
http://www.chicoree.fr