Make a boolean column unique per group

Question

I want to ensure consistency and make sure only one phone line is the default for a house.

Schema: PhoneLine
ID | isDefault | HouseID

I want to add a trigger so that when you add/update a row, and set isDefault to True, all other rows in same table with the same HouseID set isDefault to False.

I can't figure out a way to do this without creating a recursive mess.

What's the best way of approaching it?

I tried:

CREATE OR REPLACE FUNCTION unsetCurrentDefault() 
RETURNS trigger AS $unsetCurrentDefault$
    BEGIN
        IF NEW.isdefault AND NEW.houseid IS NOT NULL THEN
            -- Current ones get reset.
            UPDATE phoneline SET isdefault = false 
            WHERE houseid = NEW.houseid AND id <> NEW.id AND isdefault;
        END IF;
        RETURN NEW;
    END;
$unsetCurrentDefault$ LANGUAGE plpgsql;

CREATE TRIGGER updateDefaultValue AFTER INSERT OR UPDATE ON PhoneLine
    FOR EACH ROW EXECUTE PROCEDURE unsetCurrentDefault();

But the behaviour seems recursive.

You don't need a trigger to enforce this (off course you may use it, if you want put the updating logic in a trigger). A unique filtered index will enforce your constraint: UNIQUE (houseID) WHERE (isDefault) — ypercubeᵀᴹ, Sep 01 '16 at 15:11
Looks like you intend to reset rows with the same HouseID (not with the same ID). And it's confusing that the trigger is on a mysterious patienttable ... — Erwin Brandstetter, Sep 03 '16 at 00:28

score 3 · Accepted Answer · edited Jun 15 '20 at 09:05

The superior implementation is to save the default choice with the row in the parent table. Then a new default replaces the old one automatically. No trigger required, no chance to break referential integrity

Data model based on fragments in the question:

CREATE TABLE house (
   house_id         serial PRIMARY KEY
 , default_phone_id int
 -- , other columns
);
CREATE TABLE phoneline (
   phone_id serial PRIMARY KEY
 , house_id int  NOT NULL REFERENCES house 
 , number   text NOT NULL
 , UNIQUE (house_id, phone_id)  -- seems redundant, but needed for FK below
);
ALTER TABLE house ADD CONSTRAINT house_default_phone_id_fkey
FOREIGN KEY (house_id, default_phone_id) REFERENCES phoneline(house_id, phone_id);

The circular FK references may seem like a problem. But, really, they are not.

I made the FK constraint house_default_phone_id_fkey include house_id, so that setting the default to a phone number of a different house is impossible. The FK requires a UNIQUE (house_id, phone_id) in phoneline, which seems a bit redundant since phone_id is the PK. But you typically need an index with house_id as leading column for performance anyway.

Since the default_phone_id can be NULL, you can choose the default later, after inserting one or more phone numbers. But it even works with the column set to NOT NULL; just use data-modifying CTEs to insert & update in a single statement. Reference implementation:

How to deal with mutually recursive inserts

Make a boolean column unique per group

1 Answers1