Postgresql – use `on delete set null` on a compound foreign key which contains a primary key column

cascadeforeign keypostgresqlpostgresql-9.3

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?

Best Answer

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.

Related Question