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
