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 that postgres treats NULLs very consistently with respect to the
NULL != NULL behavior. As a result, when I define a constraint on 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 understand why.
Now, I understand that the best way to solve my problem would be to use only
non-nullable columns for my natural keys. I actually 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
