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

Re: Doubts about FK

From: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Doubts about FK
Date: 2008-09-18 11:49:20
Message-ID: 3a0028490809180449raa126a4jdf18a022f0909c55@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
2008/9/12 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> 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 do it in a transaction, so there's no problem about the other folks...!


> > 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?


>
> 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.

That's what I wanna to do!


>
>
> Note that unique constraints are not deferrable in pgsql.
>

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-09-18 12:35:47
Subject: Re: inserting boolean values in dynamic queries
Previous:From: Raphael BauduinDate: 2008-09-18 09:49:26
Subject: inserting boolean values in dynamic queries

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