From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Doubts about FK |
Date: | 2008-09-12 20:07:53 |
Message-ID: | dcc563d10809121307g4f363655p7788e3c540afb2b8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano
<rafael(dot)domiciano(at)gmail(dot)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 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:
begin;
update in a way that would normally violate an FK
insert in a way that fixes the previous statement's FK relationship
commit;
and it will work as long as the constraints all make sense by the time
you get to commit.
Note that unique constraints are not deferrable in pgsql.
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Hevia | 2008-09-12 22:01:56 | Re: Pls Hlp: SQL Problem |
Previous Message | Scott Marlowe | 2008-09-12 20:04:58 | Re: Pls Hlp: SQL Problem |