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@xxxxxxxxx> wrote: > On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb@xxxxxxxxx> 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@xxxxxxxxxxx Yahoo!: VitalyBe ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
