Skip site navigation (1) Skip section navigation (2)

Re: Doubts about FK

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 (view raw or flat)
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.

In response to

Responses

pgsql-sql by date

Next:From: Fernando HeviaDate: 2008-09-12 22:01:56
Subject: Re: Pls Hlp: SQL Problem
Previous:From: Scott MarloweDate: 2008-09-12 20:04:58
Subject: Re: Pls Hlp: SQL Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group