Should I temporarily disable foreign key constraints? How?

Question

I have two tables:

person:
    id serial primary key,
    name varchar(64) not null

task:
    tenant_id   integer not null references person (id) on delete cascade,
    customer_id integer not null references person (id) on delete restrict

(They have a lot more columns than that, but the rest aren't relevant to the question.)

The problem is, I want to cascade-delete a task when its tenant person is deleted. But when the tenant and the customer are the same person, the customer_id foreign key constraint will restrict deletion.

My question has two parts:

1. Is temporarily disabling the second foreign key my only option?
2. If so, then how do I do that in PostgreSQL?

Answer 1

Effectively you create a race condition with contradicting rules.

My first impulse was to check whether a DEFERRED constraint would help. But it makes sense that it doesn't make any difference.

I found that the FK constraint that comes first in the CREATE TABLE script is the winner of this race. If the ON DELETE CASCADE comes first, the delete is cascaded, if ON DELETE RESTRICT comes first, the operation is aborted.

db<>fiddle here
_{Old sqlfiddle}

This seems to correlate with a smaller oid in the catalog table pg_constraint:

SELECT oid, * FROM pg_constraint WHERE conrelid = 'task'::regclass

But your feedback indicates, this is not the cause. Maybe pg_attribute.attnum decides the race. Either way, as long as it is not documented behavior you cannot rely on it to stay that way in the next major version. Might be worth to post a question on [email protected].

Independent from all that, you need to consider other rows: even if CASCADE would go through for a row in task that has both tenant_id and customer_id pointing to a person, it will still be restricted if any row has only customer_id referencing person.

db<>fiddle here
_{Old sqlfiddle}

How to disable the constraint?

Your best bet is to drop and recreate it. Do it all inside a transaction to make sure you don't corrupt referential integrity.

BEGIN;

ALTER TABLE task DROP CONSTRAINT task_customer_id_fkey;

DELETE FROM person WHERE id = 3;

ALTER TABLE task ADD CONSTRAINT task_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES person (id) ON DELETE RESTRICT;

COMMIT;

This locks the table exclusively and is not fit for routine use in a multi-user environment.

How did I know the name of the constraint? I took it from pg_constraint as demonstrated above. Might be easier to use an explicit constraint name to begin with:

CREATE TABLE task (
  customer_id integer NOT NULL
, tenant_id integer NOT NULL REFERENCES person (id) ON DELETE CASCADE
, CONSTRAINT task_customer_id_fkey FOREIGN KEY (customer_id)
  REFERENCES person (id) ON DELETE RESTRICT
);

Disable trigger?

There is also:

ALTER TABLE task DISABLE trigger ALL;

More in the manual here. But that would disable all triggers. I had no luck trying to disable only the trigger created by the system to implement a single FK constraint. Targeting a specific trigger is not implemented for internal triggers.

Other alternatives would be to implement your regime with triggers or rules. That would work just fine, but those are not enforced as strictly as foreign keys.