Constraint: Only one row must have a NULL value

Question

Only one row is allowed to have parent_id NULL:

CREATE TABLE simple21_page (
    id integer NOT NULL,
    name character varying(120) NOT NULL,
    text text NOT NULL,
    parent_id integer
);

This is a tree and there should be exactly one root node.

I tried this, but it does not work:

create unique index on simple21_page (parent_id) where parent_id is null;

Is this possible with an constraint or unique index, or is a trigger needed?

Laurenz Albe · Accepted Answer · 2020-12-11 21:19:00Z

7

+100

You are almost there. To get a singleton, you need a unique constraint on a constant value:

CREATE UNIQUE INDEX ON simple21_page ((1)) WHERE parent_id IS NULL;

answered Dec 11, 2020 at 21:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2020-12-14 21:10:52Z

1

Another way to enforce this kind of uniqueness is usage of COALESCE and index on expression:

CREATE UNIQUE INDEX ON simple21_page(COALESCE(parent_id, -1));

The value provided as substitute should be out of scope of permitted values.

db<>fiddle demo

 INSERT INTO simple21_page VALUES (1, 'a', 'text', NULL);
 -- success

 INSERT INTO simple21_page VALUES (2, 'b', 'text', NULL);
 -- ERROR:  duplicate key value violates unique constraint "simple21_page_coalesce_idx"

answered Dec 14, 2020 at 21:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Constraint: Only one row must have a NULL value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related