Re: Further thoughts on Referential Integrity

On Mon, 20 May 2002, Joel Burton wrote:

<snip/>

> 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.]

This is true and the obvious answer. But since in my mental dictionary
NULL means undefined I wanted to use 0 to allow me to speed up! selects
for reports.

SELECT * From Patients WHERE Vaccine = 0;

as opposed to

SELECT * FROM Patients WHERE Vaccine IS NULL ;

Since I assume that NULL is not included in an index? I may be talking
absolute drivel here but I hope you see the logic (albeit twisted) of my
approach.

undefined then

> 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

Can you explain what the above means / is and how to do it please.

> (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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>

As regards my reference to the 'disaster' my Server, an old Sun, went tits
up yesterday when a hard disk went down, followed by the PSU!

--
Regards,

Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk