CODEWITHSUNDEEP
postgresqltriggerscascading-deletes
kcstricks
kcstricks

Reputation: 1579

Postgres: Ignore DELETE triggers for cascade deletes

I am trying to implement a relation of persons to email addresses where a person must have at least one email address at all times. The tables look like this:

CREATE TABLE persons (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE email_addresses (
    id serial PRIMARY KEY,
    person_id integer REFERENCES persons (id) ON DELETE CASCADE ON UPDATE CASCADE,
    email_address text NOT NULL
);

In order to implement the constraint that a person must have at least one email address, I thought I'd use triggers. One of the triggers necessary to satisfy the constraint is a BEFORE DELETE trigger on the email_addresses table that raises an error if the DELETE would remove the last email address for a person:

CREATE FUNCTION email_addresses_delete_trigger() RETURNS trigger AS $$
    DECLARE
        num_email_addresses integer;
    BEGIN
        num_email_addresses := (SELECT count(*) FROM email_addresses WHERE person_id = OLD.person_id);
        IF num_email_addresses < 2 THEN
            RAISE EXCEPTION 'A person must have at least one email address';
        END IF;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER email_addresses_delete_trigger BEFORE DELETE ON email_addresses
    FOR EACH ROW EXECUTE FUNCTION email_addresses_delete_trigger();

This trigger does what it is meant to do, however it prevents deletes of a person from the persons table. For example:

mydb=# DELETE FROM persons WHERE id = 1;
ERROR:  A person must have at least one email address
CONTEXT:  PL/pgSQL function email_addresses_delete_trigger() line 7 at RAISE
SQL statement "DELETE FROM ONLY "public"."email_addresses" WHERE $1 OPERATOR(pg_catalog.=) "person_id""

If I am deleting a person, then I want all their email addresses deleted too, and so I don't care if the constraint represented by the trigger is maintained during the process of the cascade delete. Is there a way to "ignore" this trigger when a person is deleted? Or is there some other way that I would need to delete a person?

Upvotes: 3

Views: 1406

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246598

My recommendation is to change the data model so that you have a not nullable foreign key constraint from persons to email_addresses that links to one of the addresses of the person. Then your requirement is automatically fulfilled, and you don't need a trigger.

That will make some things like deleting an e-mail address more complicated, but you don't have to rely on a trigger for integrity, which is always subject to race conditions.

Upvotes: 3

Related Questions