Thread: FOREIGN KEY DEFERRABLE

I have a table t1 in a Postgres database which is periodically updated
with data from a proprietary database. To date, the easiest way to update
the data is to
    begin transaction;
    delete from t1;
    insert into t1 (each row from the proprietary database);
    commit;

Table t2, with a foreign key referencing t1, is not updated. I initially
created t2 with a DEFERRABLE INITIALLY DEFERRED foreign key. However, when
I 'update' my data (by deleting, re-inserting all the rows, and committing
the transaction), Postgres complains that foreign key values in t2 still
reference t1.

Is this proper behavior?

Here's a concrete example:

create table t1 (
  a integer,
  b text,
  primary key (a)
);

create table t2 (
  c integer,
  d text,
  primary key (c),
  foreign key (c)
    references t1 (a)
    deferrable initially deferred
);

insert into t1 values (1,'some text');
insert into t1 values (2,'more text');
insert into t2 values (1,'linked text');
insert into t2 values (2,'more linked text');

begin transaction;
delete from t1;
insert into t1 values (1,'revised text');
insert into t1 values (2,'more revised text');
commit;


----
Christopher Farley
Northern Brewer / 1150 Grand Avenue / St. Paul, MN 55105
www.northernbrewer.com