Re: Doubts about FK

On Thu, Sep 18, 2008 at 5:49 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> 2008/9/12 Scott Marlowe <scott.marlowe@gmail.com>
>>
>> On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano
>> <rafael.domiciano@gmail.com> wrote:
>> > Hi there,
>> >
>> > In my DB I have a couple of FK, so the change of referenced columns is a
>> > quite complicated.
>> > Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE
>> > TRIGGER
>> > ALL to back them.
>> > Is there a better way to do that?
>>
>> Depends.  Are other people connected to the server when you do it?
>> disable trigger disables the triggers for everybody, not just you if I
>> remember correctly.  If other folks are using the db, then they can
>> insert bad data during that period.
>
> I do it in a transaction, so there's no problem about the other folks...!

Wow, I just tested this in 8.3 an the other transactions block waiting
for the one running the alter table to commit or rollback.  Cool...

>> > I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know
>> > how to
>> > use it.
>>
>> the constraints have to created as deferrable to do that.  then, in a
>> transaction, you can do something like:
>
> Can I turn my FK into deferrable FK only in that transaction?

You have to drop and recreate your constraints to make them deferrable.