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