How to represent a bi-directional list in db?

From: Pankaj Jangid <pankaj(dot)jangid(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to represent a bi-directional list in db?
Date: 2019-09-22 14:25:00
Message-ID: m2muew8l37.fsf@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I am creating an application for a manufacturing scenario. To represent
stages in an assembly line, I wanted to create following table,

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
);

But it:

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?

--
Pankaj Jangid

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2019-09-22 15:39:11 Re: How to represent a bi-directional list in db?
Previous Message Arup Rakshit 2019-09-22 13:30:38 Re: Extend inner join to fetch not yet connected rows also