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
