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
)
