Referential integrity checking issue

From: Bruno Wolff III <bruno(at)cerberus(dot)csd(dot)uwm(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Referential integrity checking issue
Date: 2001-11-01 17:24:13
Message-ID: 20011101112413.A12473@cerberus.csd.uwm.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2001-11-01 18:37:30 Re: user authentication crash by Erik Luke (20-08-2001; 1.3kb)
Previous Message Cyril VELTER 2001-11-01 16:40:46 Union View Optimization