From: Brook Milligan <brook@trillium.NMSU.Edu>
To: pgsql-sql@postgresql.org
Subject: rules and referential integrity
Date: Wed, 6 Jan 1999 08:12:05 -0700 (MST)
Message-ID: <199901061512.IAA02604@trillium.nmsu.edu> (raw)
I am trying to set up some rules to enforce referential integrity
between two tables. I can get inserts to a view that is a join of the
tables to do the right thing. A couple of problems remain, however,
and I would appreciate help.
The insert rules I have essentially say, "instead of inserting fields
in the view, insert them into the underlying tables that are joined."
In some cases, however, legitimate inserts in the join violate unique
constraints on the underlying tables and end up preventing the entire
insertion (which is bad). As a result, I want instead an insert rule
that says, "instead of inserting in the view, insert into the
underlying table if the entry is not already present and otherwise do
nothing." I tried adding a
where not exists (select id from underlying_table t where t.name = new.name)
clause in the rules (which was my attempt to prevent triggering the
rule if something existed in the underlying table) but this yielded an
unknown expression error (code 108, I think) when the rule was
triggered. Same with where id in (select id ...) clauses. Is there a
means of accomplishing this?
The delete rules should say something like, "delete if the other table
is not referencing this tuple." I tried to implement this using the
same idea of a where not exists clause but had the same problem. Is
there a means of accomplishing this?
In short, how does one enforce using rules the two parts to the
referential integrity implied by the primary_key/references A(id)
clauses?
- insert new items in the referenced table unless the key is already
used, in which case no insertion is necessary
- delete items from the referenced table unless they are referred to
by another, in which case the delete should fail
Thanks for your help.
Cheers,
Brook
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: brook@trillium.NMSU.Edu
Subject: Re: rules and referential integrity
In-Reply-To: <199901061512.IAA02604@trillium.nmsu.edu>
References: <199901061512.IAA02604@trillium.nmsu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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