db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <sylv...@chicoree.fr>
Subject Re: self referencing column
Date Sun, 14 Feb 2010 12:34:53 GMT
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



Mime
View raw message