pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

[NOVICE] SET CONSTRAINTS question...
2+ messages / 2 participants
[nested] [flat]

* [NOVICE] SET CONSTRAINTS question...
@ 2002-07-22 15:26 Michael Paesold <mpaesold@gmx.at>
  2002-07-22 15:40 ` Re: [NOVICE] SET CONSTRAINTS question... Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Paesold @ 2002-07-22 15:26 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [NOVICE] SET CONSTRAINTS question...
  2002-07-22 15:26 [NOVICE] SET CONSTRAINTS question... Michael Paesold <mpaesold@gmx.at>
@ 2002-07-22 15:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2002-07-22 15:40 UTC (permalink / raw)
  To: Michael Paesold <mpaesold@gmx.at>; +Cc: pgsql-sql


> 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

I believe we interpreted restrict as being such that you
can't defer it.  No action specification for delete
probably would do what you wanted. (We may also be wrong
about restrict, but the above would be a workaround in any case)





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-07-22 15:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-22 15:26 [NOVICE] SET CONSTRAINTS question... Michael Paesold <mpaesold@gmx.at>
2002-07-22 15:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox