Re: ALTER CONSTRAINT change action

2018-05-30 13:23 GMT-03:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:
> Currently you can't change the ON DELETE action or ON UPDATE action of an
> existing constraint. You have to drop the constraint and create it again
> with the action you want. This is not a light-weight activity, as it has to
> validate the new constraint.
>
A few weeks ago, I needed to drop/create a constraint for this same
reason: change foreign key action.

> Is there a fundamental reason that ALTER TABLE...ALTER CONSTRAINT cannot
> change the action? Or is just that no one got around to it?
>
It seems this syntax is not part of the SQL standard (at least in the
old copy I have). The ALTER CONSTRAINT clause is only useful for
constraint enforcement. AFAIK none of the popular databases has a
syntax to do this change (the recommended way is drop/create).

Change of ON DELETE/UPDATE action can have some impact in the data
model. CASCADE, SET NULL and SET DEFAULT can trigger unexpected states
(for example, joins could succeed/fail if you change the action
from/to SET NULL/DEFAULT). Someone that pretends to change a foreign
key action knows that it could change the way related data will be. I
concur that this new syntax would be useful.

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento