1

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.

  • 1
    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 1 '16 at 15:11
  • 1
    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 3 '16 at 0:28
2

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:

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.