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
