I have many tables who's natural key includes a nullable column. In this cases it's a soft-delete or 'deprecated' date
time.I'd like to add a table constraint enforcing this constraint without writing a custom procedure, but I've found
thatpostgres treats NULLs very consistently with respect to the NULL != NULL behavior. As a result, when I define a
constrainton the last two columns in these insert statements... they both succeed.
insert into mytable values (1,300, null);
insert into mytable values (1,300, null);
This is frustrating, and while there may be someone who actually wants constraints to work this way... I can't
understandwhy.
Now, I understand that the best way to solve my problem would be to use only non-nullable columns for my natural keys.
Iactually plan to do that, and use a very high value for my 'undeprecated' date to solve most of my problems related to
this.However, I can't release that version of software carelessly and I need to tighten up customer databases in the
meantime.
Is there a way to get the behavior I want?
Also, is this in compliance with SQL92? I'm surprised constraints work this way.
Thank you,
Phill