rules and referential integrity
От | Brook Milligan |
---|---|
Тема | rules and referential integrity |
Дата | |
Msg-id | 199901061512.IAA02604@trillium.nmsu.edu обсуждение исходный текст |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: