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.

Answer 1

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

Related:

• Constraint to enforce "at least one" or "exactly one" in a database
• Enforcing constraints "two tables away"