Can I use `on delete set null` on a compound foreign key which contains a primary key column?

Question

The docs say:

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key constraint, the operation will fail.

But it isn't spelled out whether on delete set null will fail if only one of the foreign key columns is nullable, or if it will merely set the nullable part to null, which is it?

Answer 1

You can't use on delete set null if any of the foreign key columns don't accept null:

create schema stack;
set search_path=stack;
--
create table t1( foo integer
               , bar integer
               , primary key(foo,bar) );
--
create table t2( foo integer
               , baz integer
               , bar integer
               , primary key (foo,baz)
               , foreign key (foo,bar) references t1 on delete set null );
--
insert into t1 values (1,1);
insert into t2 values (1,1,1);
delete from t1;
/*
ERROR:  null value in column "foo" violates not-null constraint
DETAIL:  Failing row contains (null, 1, null).
CONTEXT:  SQL statement "UPDATE ONLY "postgres"."t2" SET "foo" = NULL, "bar" = NULL WHERE $1 OPERATOR(pg_catalog.=) "foo" AND $2 OPERATOR(pg_catalog.=) "bar""
*/
--
drop schema stack cascade;

And the same is true for on delete set default. I don't know of any simple way round this, and I'm not sure I agree that it is the most sensible behaviour, I think a better choice for on delete set null would be to set the nullable columns to null if there are both.

Answer 2

You will be unable to delete anything from the referencing table as whatever happens to one part of the key happens to the others (ie: SET NULL, SET DEFAULT, etc) and that will trip the constraint if one needs to be set NULL and the other left as-is.

I had the same problem and the work-around is to have a BEFORE DELETE trigger on the referenced table.

In my case I had: ORDERS (distributor_code, order_id PK)

SHIP_TO (distributor_code, order_id FK to ORDERS, invoice_id, address_data... )

SHIP_TO also FKs to INVOICES on distributor_code, invoice_id but invoice_id could be NULL.

If I try to delete from INVOICES and there is a link, it fails.

A BEFORE DELETE trigger on INVOICES that sets invoice_id NULL but leaves distributor_code intact where (distributor_code, invoice_id) match fixes it.