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.
--
[email protected]
http://www.chicoree.fr
