Unique Index with nulls (Access ’97)

Topic

New Reply

You first have to understand Nulls. It is a common mistake to view Null as having a value of nothing. Therefore, you think that if one record has a null in its indexed fields, it will only duplicate another record having a null in the same field. This isn’t the case. Rather than thinking of Null as nothing, think of it as “could be anything”. Therefore, 2 records containing identical data and having a null in the same index field will still be accepted.

Here’s something else to consider. How would you evalutate the equation Null=Null? Is this True or False? Try it in debug and see what you get.

Reply | Quote

Replies

Thanks, Mark. That helped a lot. For anyone whose interested in creating a primary key that includes some fields that could be left blank, I did it by simply setting the “Default” property for these fields to an empty string (“”). However, note that if someone enters something into one of these fields, and then deletes it, they would get an error message (because that would leave a Null field instead of an empty string, and, of course, Nulls are not allowed in any field that’s part of your primary key). This problem can be solved by setting both the “Required” and “Allow Zero Length” properties to “Yes.” Access will automatically convert any Nulls to zero-length strings!

Reply | Quote

Yes, it will work, but it probably is not a good idea, since you can still get a “duplicate” record where the only difference is that a value is entered in that field. That doesn’t seem like a very useful primary OR unique key to me.

Reply | Quote

I want to create a primary key on 7 fields, but it’s possible for one or more of these 7 fields to be null in any given record. I’ve therefore resorted to setting up a multi-field unique index on these fields. I’ve used the Index dialogue box to name the index and define the fields, and I’ve selected “Unique” and deselected “Ignore Nulls.” The index appears to be set up correctly (I’ve even used the Help feature and gone over it step by step just to be absolutely sure), and yet I can still enter duplicate records and save the table without a problem. What’s going on? Note that I created this table by starting with an existing table that had the fields I needed and deleting the existing records.

Reply | Quote