Thread: Doubts about FK
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?
I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know how to use it.
Regards,
Rafael Domiciano
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 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.
2008/9/12 Scott Marlowe <scott.marlowe@gmail.com>
On Fri, Sep 12, 2008 at 12:14 PM, Rafael DomicianoDepends. Are other people connected to the server when you do it?
<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?
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 tothe constraints have to created as deferrable to do that. then, in a
> use it.
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.
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.