pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
rules and referential integrity
4+ messages / 3 participants
[nested] [flat]

* rules and referential integrity
@ 1999-01-06 15:12 Brook Milligan <brook@trillium.NMSU.Edu>
  0 siblings, 0 replies; 4+ messages in thread

From: Brook Milligan @ 1999-01-06 15:12 UTC (permalink / raw)
  To: 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



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

* Rules and referential integrity
@ 1999-11-12 15:13 <Miguel Sofer <mig@utdt.edu>
  0 siblings, 0 replies; 4+ messages in thread

From: " @ 1999-11-12 15:13 UTC (permalink / raw)
  To: pgsql-sql

I seem to have found a way to use the rule system for referential
integrity checks.

The manual says (in the "Rules vs Triggers part) 
"It is possible, to place a qualified rule that rewrites a query to
NOTHING if the value of a column does not appear in another table. But
then the data is silently thrown away and that's not a good idea. If
checks for valid values are required, and in the case of an invalid
value an error message should be generated, it must be done by a
trigger for now." 

Now: why not define a function (in pltcl; could be something else?)
 
 create function err(bpchar) returns bpchar 
	as 'error $1' language 'pltcl'

Then, if the value of a column does not appear in another table, you
could rewrite the query to (for instance)
      select err('your message')

This will raise a PGRES_FATAL_ERROR , with error message
ERROR:  pltcl: your message

So: it is possible. Why it is better (or worse) than a trigger, I do
not know. Views on that?







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

* Rules and referential integrity
@ 1999-11-12 15:13 <Miguel Sofer <mig@utdt.edu>
  1999-11-12 16:13 ` Re: [SQL] Rules and referential integrity Jan Wieck <wieck@debis.com>
  0 siblings, 1 reply; 4+ messages in thread

From: " @ 1999-11-12 15:13 UTC (permalink / raw)
  To: pgsql-sql

I seem to have found a way to use the rule system for referential
integrity checks.

The manual says (in the "Rules vs Triggers part) 
"It is possible, to place a qualified rule that rewrites a query to
NOTHING if the value of a column does not appear in another table. But
then the data is silently thrown away and that's not a good idea. If
checks for valid values are required, and in the case of an invalid
value an error message should be generated, it must be done by a
trigger for now." 

Now: why not define a function (in pltcl; could be something else?)
 
 create function err(bpchar) returns bpchar 
	as 'error $1' language 'pltcl'

Then, if the value of a column does not appear in another table, you
could rewrite the query to (for instance)
      select err('your message')

This will raise a PGRES_FATAL_ERROR , with error message
ERROR:  pltcl: your message

So: it is possible. Why it is better (or worse) than a trigger, I do
not know. Views on that?









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

* Re: [SQL] Rules and referential integrity
  1999-11-12 15:13 Rules and referential integrity <Miguel Sofer <mig@utdt.edu>
@ 1999-11-12 16:13 ` Jan Wieck <wieck@debis.com>
  0 siblings, 0 replies; 4+ messages in thread

From: wieck@debis.com @ 1999-11-12 16:13 UTC (permalink / raw)
  To: mig@utdt.edu; +Cc: pgsql-sql

> Now: why not define a function (in pltcl; could be something else?)
>
>  create function err(bpchar) returns bpchar
>    as 'error $1' language 'pltcl'
>
> Then, if the value of a column does not appear in another table, you
> could rewrite the query to (for instance)
>       select err('your message')
>
> This will raise a PGRES_FATAL_ERROR , with error message
> ERROR:  pltcl: your message
>
> So: it is possible. Why it is better (or worse) than a trigger, I do
> not know. Views on that?

    Depends  on  the  data  and  usage.  If  only  few tuples are
    affected by a statement, a trigger will usually do  a  better
    job.  Especially  if  the  path  to  find  that tuple is very
    complicated, because the rule has to follow this path another
    time.

    OTOH  if one statement affects thousands of rows, a rule will
    perform better because it is executed in one  internal  extra
    query  instead of invoking the trigger for each single tuple.

    There is no general "this is better than that".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





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


end of thread, other threads:[~1999-11-12 16:13 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-06 15:12 rules and referential integrity Brook Milligan <brook@trillium.NMSU.Edu>
1999-11-12 15:13 Rules and referential integrity <Miguel Sofer <mig@utdt.edu>
1999-11-12 15:13 Rules and referential integrity <Miguel Sofer <mig@utdt.edu>
1999-11-12 16:13 ` Re: [SQL] Rules and referential integrity Jan Wieck <wieck@debis.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