Thread: referential integrity problem

Hi,

Here is my problem:

CREATE TABLE users (
id        SERIAL        PRIMARY KEY
);

CREATE TABLE orders (
id        SERIAL        PRIMARY KEY,
user_id        INT        NOT NULL REFERENCES users ON DELETE CASCADE
);

CREATE TABLE orders_log (
order_id    INT        NOT NULL REFERENCES orders ON DELETE CASCADE,
by_user    INT        REFERENCES users ON DELETE SET NULL
);

Now suppose i do:

INSERT INTO users DEFAULT VALUES;
INSERT INTO orders (user_id) VALUES (currval('users_id_seq'));
INSERT INTO orders_log (order_id, by_user) VALUES (currval('orders_id_seq'),
currval('users_id_seq'));
DELETE FROM users WHERE id=currval('users_id_seq');

That last delete gives me a referential integrity violation error.
I've figured out that if I mark the by_user reference as INITIALLY DEFERRED,
then it works fine.  But I don't understand why it refuses to work as is.
I use PostgreSQL 7.1.3

On Sun, 17 Feb 2002, Joseph Artsimovich wrote:

> Here is my problem:
>
> CREATE TABLE users (
> id        SERIAL        PRIMARY KEY
> );
>
> CREATE TABLE orders (
> id        SERIAL        PRIMARY KEY,
> user_id        INT        NOT NULL REFERENCES users ON DELETE CASCADE
> );
>
> CREATE TABLE orders_log (
> order_id    INT        NOT NULL REFERENCES orders ON DELETE CASCADE,
> by_user    INT        REFERENCES users ON DELETE SET NULL
> );
>
> Now suppose i do:
>
> INSERT INTO users DEFAULT VALUES;
> INSERT INTO orders (user_id) VALUES (currval('users_id_seq'));
> INSERT INTO orders_log (order_id, by_user) VALUES (currval('orders_id_seq'),
> currval('users_id_seq'));
> DELETE FROM users WHERE id=currval('users_id_seq');
>
> That last delete gives me a referential integrity violation error.
> I've figured out that if I mark the by_user reference as INITIALLY DEFERRED,
> then it works fine.  But I don't understand why it refuses to work as is.
> I use PostgreSQL 7.1.3

I think this is possibly the known bug where intermediate states can
sometimes been seen by the referential integrity constraints.  I believe
that part of a patch I'd sent to -patches a while back would probably
correct the situtation.  I didn't try applying it to 7.1, but it probably
would apply.  You can probably find it in the archives, or I can try to
find it and send it if you can't.