Referential integrity checking issue
I am having a problem where I want to delete all of the existing information
in a database and reinitialize it in a transaction. I am reusing the
primary key values and this results in an error with referential
integrity checking.
I think I have seen something similar to this discussed here previously, but
I am not sure if it was exactly the same problem.
Are things supposed to work like this?
sql commands to reproduce the problem:
create table test1 (col1 int primary key);
create table test2 (col1 int references test1 deferrable);
insert into test1 values (1);
insert into test2 values (1);
begin;
set constraints all deferred;
delete from test1;
delete from test2;
insert into test1 values (1);
insert into test2 values (1);
commit;
drop table test1;
drop table test2;
Output when running the commands with psql:
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test1_pkey' for table 'test1'
CREATE
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT 1788879 1
INSERT 1788880 1
BEGIN
SET CONSTRAINTS
DELETE 1
DELETE 1
INSERT 1788881 1
INSERT 1788882 1
ERROR: <unnamed> referential integrity violation - key in test1 still referenced from test2
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "test2"
DROP
DROP
On Thu, 1 Nov 2001, Bruno Wolff III wrote:
I am having a problem where I want to delete all of the existing information
in a database and reinitialize it in a transaction. I am reusing the
primary key values and this results in an error with referential
integrity checking.I think I have seen something similar to this discussed here previously, but
I am not sure if it was exactly the same problem.
I believe so.
Are things supposed to work like this?
Not really. What's happening I believe is that it's looking at the final
state of the database and seeing that a row in test2 matches. It then
also needs to determine if a matching row was re-inserted into test1
which it doesn't currently do. Part of the reason for this was a mistake
in reading a piece of the spec that made it appear that such constructs
were illegal, so they weren't coded for.
I have a test patch that I think fixes the base constraint and the no
action referential actions against a 7.2 but it should probably apply
okay against 7.1.x. (I think I sent it to the list a while back,
if not you can write me).