"Daniel Howard" <cheeserolls@yahoo.com> writes:
> The command
> SET CONSTRAINTS ALL DEFERRED
> seems to have no effect.
Yes it does. For instance, in your example setting the mode to deferred
will allow you to insert an items row that doesn't match any users row:
regression=# insert into items(user_id) values(42);
ERROR: insert or update on table "items" violates foreign key constraint "items_user_id_fkey"
DETAIL: Key (user_id)=(42) is not present in table "users".
regression=# begin;
BEGIN
regression=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
regression=# insert into items(user_id) values(42);
INSERT 0 1
regression=# commit;
ERROR: insert or update on table "items" violates foreign key constraint "items_user_id_fkey"
DETAIL: Key (user_id)=(42) is not present in table "users".
regression=#
What you wrote is
> CREATE TABLE items (id serial PRIMARY KEY, user_id integer NOT NULL
> REFERENCES users ON DELETE RESTRICT DEFERRABLE, itemname text);
The ON DELETE RESTRICT part is a "referential action", not a constraint
as such. Our reading of the SQL standard is that referential actions
happen immediately regardless of deferrability of the constraint part.
So that's why you get an error on deletion of a users row.
regards, tom lane