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