Re: Further thoughts on Referential Integrity

"Joel Burton" <joel(at)joelburton(dot)com> writes:
> 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).

There's also option 4:

* use NULLs, and make a partial index using "WHERE foo IS NULL" to
catalog the rows containing nulls.

The cost of maintaining the extra index wouldn't be worth it unless
looking for the null rows is done very frequently ... but it is another
option to consider.

regards, tom lane