Referential Integrity.

On Mon, 20 May 2002, Wm. G. Urquhart wrote:

Hi All,

After yesterday's disaster I've taken this opportunity to rebuild by
database from the ground up. As part of this I want to be able to set a
column to have a default of 0 (easy enough) but this column is a Foreign
Key to another table whos referenced column value starts at 1.

Is there anyway I can write a FK Constraint that will allow this? I don't
want to have to enter a row in the referenced table since since it in turn
has not null constraints on it.

I really want a way to allow 0 as a legitimate value, to assist in the
predicates of a SELECT. It is possible to get to and modify the code that
PostgreSQL creates to enforce this I assume via CREATE CONSTRAINT TRIGGER?

You could make a copy of the trigger functions in
backend/utils/adt/ri_triggers.c and change them to do what you want. I'm
assume that NULL in the foreign key table in unacceptable since a NULL
referencing column always works.