Thread: ALTER CONSTRAINT change action

2018-05-30 13:23 GMT-03:00 Jeff Janes <jeff.janes@gmail.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