unique index still allow duplicates when date fields are null

[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

 

[Allen Browne]

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.

 

[Jerry Whittle]

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.