[ a couple of random thoughts after quickly scanning this thread ... ]
David Christensen <david.christensen@crunchydata.com> writes:
> I assume this would look something like:
> ALTER TABLE foo ALTER CONSTRAINT my_fkey ON UPDATE CASCADE ON DELETE RESTRICT
> with omitted referential_action implying preserving the existing one.
I seem to remember somebody working on exactly that previously, though
it's evidently not gotten committed. In any case, we already have
ALTER TABLE ... ALTER CONSTRAINT constraint_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
which has to modify pg_trigger rows, so it's hard to see why it'd
be at all difficult to implement this using code similar to that
(maybe even sharing much of the code).
Returning to the original thought of a DML statement option to temporarily
override the referential_action, I wonder why only temporarily-set-CASCADE
was considered. It seems to me like there might also be use-cases for
temporarily selecting the SET NULL or SET DEFAULT actions.
Another angle is that if we consider the deferrability properties as
precedent, there already is a way to override an FK constraint's
deferrability for the duration of a transaction: see SET CONSTRAINTS.
So I wonder if maybe the way to treat this is to invent something like
SET CONSTRAINTS my_fk_constraint [,...] ON DELETE referential_action
which would override the constraints' action for the remainder of the
transaction. (Permission needs TBD, but probably the same as you
would need to create a new FK constraint on the relevant table.)
In comparison to the original proposal, this'd force you to be explicit
about which constraint(s) you intend to override, but TBH I think that's
a good thing.
One big practical problem, which we've never addressed in the context of
SET CONSTRAINTS but maybe it's time to tackle, is that the SQL spec
defines the syntax like that because it thinks constraint names are
unique per-schema; thus a possibly-schema-qualified name is sufficient
ID. Of course we say that constraint names are only unique per-table,
so SET CONSTRAINTS has always had this issue of not being very carefully
targeted. I think we could do something like extending the syntax
to be
SET CONSTRAINTS conname [ON tablename] [,...] new_properties
Anyway, just food for thought --- I'm not necessarily set on any
of this.
regards, tom lane