db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From joe <fischauto...@yahoo.de>
Subject Re: self referencing column
Date Tue, 02 Feb 2010 09:33:03 GMT
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
)


Mime
View raw message