unique index still allow duplicates when date fields are null

  • Thread starter Thread starter theelio
  • Start date Start date
T

theelio

I have an access 2003 database. there are many places in my program where i
add records into one table, in order to avoid testing duplicated records on
each insert i find it better to create a unique index on many fields of
different data type like long integer , text and date/time but it seems that
when the date/time fields are null it allows the duplication even if i
precise to the index to not ignore nulls
Any help would be much apreciated
Thank you
 
The Ignore Nulls property of the index specifies only whether the null
fields should be indexed. It does not require that only one record can have
a null value in the field.

That makes sense if you think of null as meaning Unknown or not applicable.
Two records can have unknown dates, but that's not a duplicate.
Two records can have a not applicable date (e.g. DeathDate for people still
living), but that is not a duplicate.
 
To add to what Allen said, one of the differences between a primary key and a
unique constraint/index is that a PK does not allow nulls.
 
Back
Top