Search code examples
postgresql

Setting a composite foreign key to NULL in Postgres

I have two tables, parents and children, and a column in children called parent. I also have a column called account_id in both tables that should be NOT NULL.

I also have a composite foreign key like this:

ALTER TABLE children
    ADD COLUMN IF NOT EXISTS parent UUID,
    ADD CONSTRAINT parent_id FOREIGN KEY (account_id, parent) REFERENCES parents(account_id, id)
    ON UPDATE RESTRICT ON DELETE SET NULL;

Upon deleting a row from parents, I want the child to remain in the children table, but with a parent of NULL. However, the above migration causes an error in the case of a delete, complaining that the value of account_id cannot be NULL. How can I rewrite my constraint to achieve this?

Solution

  • You cannot do that with ON DELETE SET NULL. You will have to define a BEFORE UPDATE trigger on parents that sets parent on the referencing rows in children to NULL.