Hello,
We have a problem related to transactions and constraints. Our tables
look like this:
---
CREATE TABLE table_a
(
id INT NOT NULL PRIMARY KEY
);
CREATE TABLE table_b
(
a_id INT NOT NULL CONSTRAINT my_ref
REFERENCES table_a(id)
DEFERRABLE INITIALLY DEFERRED,
value INT NOT NULL
);
---
In psql we run the following SQL statements:
---
BEGIN;
INSERT INTO table_a (id) VALUES (1);
INSERT INTO table_b (a_id,value) VALUES (1,1);
DELETE FROM table_b WHERE a_id=1;
DELETE FROM table_a WHERE id=1;
COMMIT;
---
Upon commit, we get the following error message:
"ERROR: my_ref referential integrity violation - key referenced from
table_b not found in table_a"
We believe this to be valid SQL. We have successfully executed the
equivalent statements on an Informix Dynamic Server 9.20.
Our environment:
Linux palpatine 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686
unknown
PostgreSQL 7.2.1 built from yesterday's source RPMS on
www.postgresql.org
Does anyone know if the error is on ours or on PostgreSQL's side?
TIA,
--
Emil Eifrem [emil@windh.net] /'\ ASCII Ribbon Campaign
Kernel Developer, .windh AB \ / No HTML/RTF in email
× No Word docs in email
/ \ Respect for open standards
