Обсуждение: Alter Contraint

    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. I'm not
really sure why deferrable is not the default.
    Many thanks

Peter Childs

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