Re: Further thoughts on Referential Integrity

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Wm. G. Urquhart
> Sent: Monday, May 20, 2002 9:07 AM
> To: PostgreSQL General Forum
> Subject: [GENERAL] Further thoughts on Referential Integrity
>
>
> I'm going to show my ignorance of PortgreSQL here since I've only been
> using it for a week! But I've been reading the documentation and I've came
> across CREATE RULE. I assume from what I understand it would be possible
> to implement my DEFAULT 0 using a rule.
>
> I'd need to remove the actual constraint from the table column and then
> use an INSERT / UPDATE rule to check to see if the column was 0 then I
> ignore it, if it's not; then I check to see if its in the 'referenced'
> table.
>
> Would this work or am I asking for trouble?

Both. :)

Why are you not storing NULL in the table, rather than 0? This would require
no additional work on your part and would let you use the built-in RI
features. Plus, with a VIEW, you could always show 0 rather than NULL
(COALESCE ...). [You referenced a disaster yesterday, but don't seem to have
posted to the list about it.]

If you *really* want to store 0 rather than NULL, you could either:

(a) add a 0-value column in the referenced table (cheating but easy and
straightforward -- I used to call these "orphanages": places to keep an
records that would otherwise be RI orphans), or

(b) re-write the PG referential integrity features using triggers. Put
BEFORE triggers for INSERT and UPDATE on the child table, and on DELETE on
the parent table. The triggers should call plpgsql functions that check for
the related values and either raise an exception or allow it. This isn't
that much work, but: a) it's non-standard (much easier to understand later
if you use the built-in RI, plus you get CASCADE features, DEFERRABLE
options, etc.), and b) it will be much slower than the built-in versions
(which are written in C).

Out of these options, I'd strongly recommend a preference of traditional
NULLs for unknown, then adding a 0-value column, and re-writing RI only if
you have a _really_ good reason. Do you?

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant