Problem:
--------
In the following scenario, my intent had been to ensure that 'action' records could optionally
refer to 'flag' records, but only so long as both the 'action' and 'flag' belonged to the
same 'flow'.
My on-delete-cascade rule is 'set null', with the intent of setting the 'flag' field back to null,
which is sufficient for "no flag selected" situations. The result, however, is that deleting
a 'flag' record attempts to set-null on all related 'action' records, and that sets both 'flag'
and 'flow' to null since both fields are part of the FK; that, in turn, causes the not-null
constraint on the 'flow' field to fail (it's part of the PK), aborting the whole operation.

create table dg_actions (
flow varchar(50) not null,
code varchar(50) not null,
name varchar(50),
constraint dg_fk_actions_flows foreign key (flow) references dg_flows (code) on update cascade on
delete cascade,
flag varchar(50),
constraint dg_fk_actions_flags foreign key (flag, flow) references dg_flags (code, flow) on update
cascade on delete set null,
constraint dg_pk_actions primary key (code, flow),
);

Proposal:
---------
I understand why it's doing it, and it all seems to work according to spec, but does anyone see a
problem with requesting (as a feature upgrade -- this is not a bug report!) that on-cascade-set-
null rules only attempt to set-null on fields that are actually nullable? As long as at least one
field of a multi-field FK is nullable, such a cascade rule makes sense, it will have the desired
effect of 'unlinking' the records. (I wouldn't ask for it to verify this up-front, or handle weird
constraints on the PK fields, or handle 'set default' rules similarly, as you could easily have
opaque CHECK constraints with the same effect as 'not null' without actually declaring it, or all
sorts of other per-field weirdness. The 'nullable' flag is special though, and 'null' is a special
case in FK's...)

An alternate proposal would be this syntax:

constraint dg_fk_actions_flags foreign key (flag, flow) references dg_flags (code, flow) on update
cascade on delete set null (flag),

Work-arounds:
-------------
I can't use calculated fields in an FK, and now that I'm thinking about cascade rules, that makes a
good bit of sense. (Same for using 'constants' in FK's, another feature I've seen requested here
and there, for cases where you just want to say "the related record must be of type 6". Being able
to tell the system exactly which fields to set-null or set-default could help with all of this
though, so it doesn't try to set-null a calculated non-updatable field, etc.)

Another solution would be to create a second 'flow' field, add a trigger to copy values from one to
the other, and then use one as part of the PK, and the other as part of the FK. That seems rather
messy to me, and I'd need to make either 'flow' field updatable, always updating the other one.
Surely this situation comes up a lot ("make sure that records are correctly related" in a 3- or 4-
part system, such as the classic "class/class_member/object/object_member" scenario, where
object_member refers to a class_member, which must be a member of the same class of which the
related object is an instance, etc.)

Or I guess I could put a trigger on the 'flag' table that goes and clears the 'flag' field of
related 'action' records. That would preserve the FK constraint, which would still protect me in
concurrent-transaction scenarios, but I'd essentially do the cascade's work for it.

Thanks:
-------
I can live without it, certainly. Just seems like it'd be a neat "intelligence" to build in, as
long as noone sees ill effects.

-Philip