Multi Column Unique Index with nulls

[Doc]

Trying to setup a multi field unique index with some of the columns allowing
null values. If all columns are filled in, index catches a duplicate entry,
if all columns but those which do not require entry are filled in, index does
not catch duplicate entries.

ID - Auto # and Primary
F1 - Text - required
F2 - Text - required
F3 - Text - required
F4 - text - not required

Index: F1, F2, F3, F4
Primary = NO
Unique = YES
Ignore Nulls = NO

Index catches:

1 - A - B - C - D
2 - A - B - C - D

Does not catch:

1 - A - B - C -
2 - A - B - C -


How can I make sure the index catches the second example without requiring
input in F4?


Thanks!

 

[George]

Your problem is that fourth field because litterally "anything" can be in
it, including nothing. That's not just a bad pun.

If the field is not required, then it can be Null. Null is an important
concept in database design and getting familiar with it will help you, not
just here, but in many other situations.

Null can be understood, in this context, as the "absence of information".
There will, no doubt, be math and database experts willing to explain why
that is an oversimplification and probably not even accurate, but that seems
to be a reasonable way to take a first run a the concept as it applies here.

When the field is Null (and it is not appropriate to say it has a Null
value, because, well, Null is not a value), then you can't decide if it is
unique or not because it can't be compared to anything else. You don't have
the information needed to make the comparison. You can't say, by pointing to
nothing, that it is is the same as, or different from, ANYTHING else, even
another "nothing".

Either require a value in that field or make the unique index based the
three required fields, or as your own example shows, add a field (the 1 and
2) and make it part of the unique index.

HTH

George

 

[Doc]

Thanks George, worked like a charm. I ended up requiring a entry in all
fields like you suggested, and just set the default value to "". That way if
two zero length entries are attempted, it flags the unique index.

Thanks again!