You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by joe <fi...@yahoo.de> on 2010/02/01 10:21:07 UTC
self referencing column
Hi,
i have to persist a simple state machine.
my problem is how to insert the first state which should reference itself.
CREATE TABLE state
(
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
INCREMENT BY 1) constraint STATE_ID_PK primary key,
NEXT_STATE_ID integer not null constraint STATE_NEYT_STATE_ID
REFERENCES STATE
)
insert into state values (default, default)
doesn't work: "Column 'NEXT_STATE_ID' cannot accept a NULL value."
Re: self referencing column
Posted by joe <fi...@yahoo.de>.
Hi,
Hm i hoped for a one liner ;)
>
> If you want the table to be an ourobouros, you can patch the first row
> like this:
>
> update state set next_state_id = ( select max( id ) from state ) where
> id = 1;
>
> and you can add the "not null" constraint afterwards:
>
> alter table state alter column next_state_id not null;
>
Forget to mention, its not just the first state which can reference itself.
The user has an UI where he can enter new states and define which is the
next state.
The next state can always be the state itself.
Currently i tend toward a solution where null is used as a special value
for self reference, so removing the not null constraint.
CREATE TABLE state
(
ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT
BY 1) constraint STATE_ID_PK primary key,
NEXT_STATE_ID int constraint STATE_NEYT_STATE_ID REFERENCES state
)
Re: self referencing column
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Joe,
If you remove the "not null" constraint from the next_state_id column
then you will be able to insert your first row:
create table state
(
id integer not null generated always as identity (start with 1,
increment by 1) constraint state_id_pk primary key,
next_state_id integer constraint state_next_state_id references state
);
insert into state( next_state_id ) values ( null );
insert into state( next_state_id ) values ( 1 );
If you want the table to be an ourobouros, you can patch the first row
like this:
update state set next_state_id = ( select max( id ) from state ) where
id = 1;
and you can add the "not null" constraint afterwards:
alter table state alter column next_state_id not null;
Hope this helps,
-Rick
joe wrote:
> Hi,
>
> i have to persist a simple state machine.
> my problem is how to insert the first state which should reference
> itself.
>
> CREATE TABLE state
> (
> ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
> INCREMENT BY 1) constraint STATE_ID_PK primary key,
> NEXT_STATE_ID integer not null constraint STATE_NEYT_STATE_ID
> REFERENCES STATE
> )
>
>
> insert into state values (default, default)
>
> doesn't work: "Column 'NEXT_STATE_ID' cannot accept a NULL value."
>
>
Re: self referencing column
Posted by Sylvain Leroux <sy...@chicoree.fr>.
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
Re: self referencing column
Posted by joe <fi...@yahoo.de>.
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.
Re: self referencing column
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
joe <fi...@yahoo.de> writes:
> Hi,
>
> i have to persist a simple state machine.
> my problem is how to insert the first state which should reference itself.
>
> CREATE TABLE state
> (
> ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
> INCREMENT BY 1) constraint STATE_ID_PK primary key,
> NEXT_STATE_ID integer not null constraint STATE_NEYT_STATE_ID
> REFERENCES STATE
> )
Perhaps another way to work around this is to use GENERATED BY DEFAULT as
IDENTITY.
Dag
>
>
> insert into state values (default, default)
>
> doesn't work: "Column 'NEXT_STATE_ID' cannot accept a NULL value."