ALTER CONSTRAINT change action
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.
Is there a fundamental reason that ALTER TABLE...ALTER CONSTRAINT cannot
change the action? Or is just that no one got around to it?
Cheers,
Jeff
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