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?

Answer 1

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;