I tried looking into your solution.. However, the "DEFERRABLE
INITIALLY DEFERRED" doesn't seem to act as I expect it to. I made two
sample tables:
--------------------------------------------------------------------------------
CREATE TABLE functions.temp1
(
id1 int4 NOT NULL,
id2 int4,
CONSTRAINT pk_temp1 PRIMARY KEY (id1),
CONSTRAINT temp2_id2 FOREIGN KEY (id2) REFERENCES functions.temp2
(id2) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY
DEFERRED
)
WITHOUT OIDS;
CREATE TABLE functions.temp2
(
id2 int4 NOT NULL,
CONSTRAINT temp2_pk PRIMARY KEY (id2),
CONSTRAINT temp2_id2_key UNIQUE (id2)
)
WITHOUT OIDS;
--------------------------------------------------------------------------------
Then I tried to run the following SQL:
begin;
delete from temp2;
delete from temp1;
end;
Based on what the documentation says, this transaction should've
worked, but instead all I get is:
ERROR: update or delete on "temp2" violates foreign key constraint
"temp2_id2" on "temp1"
DETAIL: Key (id2)=(1) is still referenced from table "temp1".
Have I done anything wrong?
On Sat, 26 Feb 2005 19:56:32 -0600, George Essig <george.essig@gmail.com> wrote:
> On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb@gmail.com> wrote:
> > So basically what I have is:
> >
> > public schema, in which there are two tables, A and B.
> > backup schema, in which there are two tables, A and B.
> >
> > On table A and B in public I add a trigger "On Delete" which inserts
> > the deleted data to the matching tables in the backup scehma.
> >
> > That'd work fine except the foreign keys problem. In A I have a
> > column, "B_id" that is a foreign key to an "id" in the B table. Thus
> > it means that I have to delete from A before I delete from B.
> >
> > Inserting into the backup folders, on the other hand, should be
> > reversed, from the same reasons.
> >
> > Anyone has an idea how to solve this?
> >
>
> Maybe a DEFERRABLE INITIALLY DEFERRED foreign key would work. This
> will cause the foreign key constraint to be checked at the end of the
> transaction. See the manual at:
>
> http://www.postgresql.org/docs/8.0/static/sql-createtable.html
>
> George Essig
>
--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent@hotmail.com
Yahoo!: VitalyBe