Re: How to represent a bi-directional list in db?

Pankaj:

On Sun, Sep 22, 2019 at 4:25 PM Pankaj Jangid <pankaj(dot)jangid(at)gmail(dot)com> wrote:
> CREATE TABLE stages (
> id SERIAL PRIMARY KEY,
> name VARCHAR(80) NOT NULL,
> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
> prev_stage_id SERIAL REFERENCES stages NULL,
> next_stage_id SERIAL REFERENCES stages NULL,
> process_id SERIAL REFERENCES processes NOT NULL
> );
> Failed with: conflicting NULL/NOT NULL declarations for column
> "prev_stage_id" of table "stages"
> Is it not possible to create "nullable" self referencing foreign keys?

Serial seems wrong. It means integer, not null, defaul next value from
a sequence.

What you probably want is just "prev_stage_id INTEGER" ( NULL by
default ), as you do not want the prev/next stage ids to be generated,
you normally would want to assign values from other tuples.

Also, you may have problems populating this kind of table, as you will
not have the ids from either prev or next stage when building it.

And lastly, in SQL you do not really need a doubly linked list, just
populate prev_stage_id, and index it and you can query next stage of a
tuple using it.

Francisco Olarte.