db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: self referencing column
Date Mon, 01 Feb 2010 14:17:50 GMT
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."
>
>


Mime
View raw message