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."