Alter Contraint

On Fri, 7 Feb 2003, Peter Childs wrote:

Now we can actually use Constraints properly in 7.3 (unlike
previous versions) Is it possible to change them without dropping and
recreating.

Not really, unfortunately.

I think somone else has already asked this within the last few
days. But what I need to do is make my constraints deferrable. I'm not
really sure why deferrable is not the default.

AFAICT, SQL92 requires INITIALLY IMMEDIATE NOT DEFERRABLE as the
default (10.6 SR3 & 4b).

Peter Childs <blue.dragon@blueyonder.co.uk> writes:

Now we can actually use Constraints properly in 7.3 (unlike
previous versions) Is it possible to change them without dropping and
recreating.
I think somone else has already asked this within the last few
days. But what I need to do is make my constraints deferrable.

Have you looked at SET CONSTRAINTS ? I'm not sure how complete our
implementation is, but it seems more nearly a match for your problem
than a persistent ALTER command.

I'm not really sure why deferrable is not the default.

Because the SQL spec says so.

regards, tom lane

On Fri, 7 Feb 2003, Tom Lane wrote:

Peter Childs <blue.dragon@blueyonder.co.uk> writes:

Now we can actually use Constraints properly in 7.3 (unlike
previous versions) Is it possible to change them without dropping and
recreating.
I think somone else has already asked this within the last few
days. But what I need to do is make my constraints deferrable.

Have you looked at SET CONSTRAINTS ? I'm not sure how complete our
implementation is, but it seems more nearly a match for your problem
than a persistent ALTER command.

AFAIK, SET CONSTRAINTS only lets you change the check time of
deferrable constraints, it doesn't let you make a not deferrable
constraint deferrable.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

AFAIK, SET CONSTRAINTS only lets you change the check time of
deferrable constraints, it doesn't let you make a not deferrable
constraint deferrable.

Good point, if your constraint wasn't deferrable to begin with then
you can't make it so with SET. But on the other hand, making it so
is a one-time operation, so deleting and recreating the constraint
doesn't seem that big a deal. If I recall Peter's original message,
he was mainly concerned about flipping from the not-deferred to deferred
state and back efficiently --- that's what SET seems designed for.

I have nothing against providing an ALTER if someone wants to do the
legwork, but it doesn't seem like a high-priority problem ...

regards, tom lane

unsubscribe