From: | Christopher Farley <chris(at)northernbrewer(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FOREIGN KEY DEFERRABLE |
Date: | 2000-11-15 05:53:23 |
Message-ID: | Pine.BSF.4.21.0011142318210.67183-100000@kraeusen.nbrewer.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | carl garland | 2000-11-15 08:40:28 | Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL |
Previous Message | Ashley Clark | 2000-11-15 05:49:00 | Re: Can this be done? |