Constraint: Only one row must have a NULL value

user_633961 0
Viewed 261

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?

postgresql
2 Answers

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

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"
user_50708790
Related