Re: Further thoughts on Referential Integrity

> -----Original Message-----
> From: Wm. G. Urquhart [mailto:wgu@wurquhart.co.uk]
> Sent: Monday, May 20, 2002 9:48 AM
> To: Joel Burton
> Cc: PostgreSQL General Forum
> Subject: RE: [GENERAL] Further thoughts on Referential Integrity
>
>
> On Mon, 20 May 2002, Joel Burton wrote:
>
> > 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 ...).
>
> 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.

Finding NULL integers in a table does take longer than finding 0 values when
an index is used, so, yes, you're right here.

create table lotsaints(i int);

create function fillints(int) returns int as 'begin for i in 1 .. $1 loop
insert into lotsaints values ( (random()*10000)::int+1 ); end loop; return
$1; end' language plpgsql;

select fillints(10000);

create function addstuff(int) returns int as 'begin for i in 1 .. $1 loop
insert into lotsaints values (0); insert into lotsaints values (null); end
loop; return $1; end' language plpgsql;

select addstuff(160);

create index lotsaints_idx on lotsaints(i);
vacuum lotsaints;
set enable_seqscan=off;

explain analyze select * from lotsaints where i=0;       -- 3.14 msec
explain analyze select * from lotsaints where i is null; -- 15.19 msec


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

Let's assume that this is a customers -> orders database.

It's a simple idea: in the customers table, add a fake customer with PK=0.
It's then legal to reference this from the child table. You can either
exclude this customer in your SELECT statements or (better) write a VIEW
that does this for you, and work from the view.

--

So, I'd say you _still_ have three options:

* use NULLs (which will be slower by a bit when the index is being used),

* use an orphanage (which will be slower by a __tiny__ bit for all selects,
because you'll be adding one more exclusion to your WHERE clause)

* or re-write RI (which will be much slower for INSERTs, DELETEs, UPDATEs,
and might be a bit more of a pain to port to other systems or maintain).

Unless you very rarely make changes to the table or update speeds don't
matter, I wouldn't do the RI-rewriting.

I, personally, would pick the first option in most cases, but I have a
fairly fast server, fairly small data sets, and I like the clean approach. A
few microseconds here or there won't add up to much for me. But if you have
lots of data, the second is probably your best bet.

HTH.

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