Thread: SET CONSTRAINTS question...

SET CONSTRAINTS question...

From
"Michael Paesold"
Date:
Hi,

Right after installting postgres 7.2.1 (from release source) and reading
some chapters of Bruce Momjian's great book I started to play around with a
test database. Now I have a question concerning deferred constraint
checking.
I am new to transactional SQL, so I don't really know, if this should work:

-- create query
BEGIN;

CREATE TABLE friend (
 name VARCHAR(40) PRIMARY KEY,
 country CHAR(2) NOT NULL DEFAULT 'AT'
);

CREATE SEQUENCE conn_id_seq;
CREATE TABLE conn (
 id INTEGER NOT NULL DEFAULT nextval('conn_id_seq'),
 name VARCHAR(40),
 CONSTRAINT conn_friend_fkey FOREIGN KEY(name)
   REFERENCES friend(name)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
   DEFERRABLE INITIALLY IMMEDIATE,
 CONSTRAINT conn_pkey PRIMARY KEY(id)
);
CREATE INDEX conn_name_idx ON conn (name);

INSERT INTO friend VALUES ('Michael Paesold', 'AT');
INSERT INTO conn (name) VALUES ('Michael Paesold');

COMMIT;

-- test
BEGIN;
SET CONSTRAINTS conn_friend_fkey DEFERRED;
DELETE FROM friend WHERE name='Michael Paesold';
INSERT INTO friend VALUES ('Michael Paesold', 'DE');
COMMIT;


I get an error just after the DELETE query. Perhaps I don't understand the
concept of deferred constraint checking. I know that the above query doesn't
make much sense, but I wanted to try these features.

Best Regards,
Michael Paesold