Primary Index and Null values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cyberwolf - 21 Mar 2006 14:56 GMT

Is there a way to set a primary index to ignore Nulls and if so how
would I do it. I have already tried setting it so it is not a required
field, but that does not work. It states that the field can not
contain null values.

Reply to this Message

Allen Browne - 21 Mar 2006 15:20 GMT

A primary key is a field that ensures uniqueness.

The null value is unknown.
If it is unknown, it cannot be said to be unique.

Therefore a Null in a primary key is a contradiction in terms.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Is there a way to set a primary index to ignore Nulls and if so how
> would I do it. I have already tried setting it so it is not a required
> field, but that does not work. It states that the field can not
> contain null values.

Reply to this Message

Anthony England - 21 Mar 2006 15:30 GMT

You cannot do this for your primary key.
If you want a field to have a unique index but ignore nulls, then you can
have this but it is not the primary key. Just set the properties:
Required=No
Indexed=Yes (No Duplicates)

Reply to this Message

paii, Ron - 21 Mar 2006 15:51 GMT

> > Is there a way to set a primary index to ignore Nulls and if so how
> > would I do it. I have already tried setting it so it is not a required
[quoted text clipped - 6 lines]
> Required=No
> Indexed=Yes (No Duplicates)

(No Duplicates) could fail if more then one record had Null in the field.
Also any new records after the 1st Null would require a value for the insert
to succeed.

Reply to this Message

Anthony England - 21 Mar 2006 16:02 GMT

>> > Is there a way to set a primary index to ignore Nulls and if so how
>> > would I do it. I have already tried setting it so it is not a required
[quoted text clipped - 11 lines]
> insert
> to succeed.

I just created a table with two fields:
F1 = Long Integer, Not Required, Indexed No Duplicates, Not Required
F2 = Text, Required, Do Not Allow Zero Length, Not Indexed

I could enter the following records:

ID F1
1 ABC
Null DEF
Null GHI
2 ABC

However, any attempt to add another record with ID=1 or ID=2 is rejected.

Could you be mistaken in your assertion?

Reply to this Message

paii, Ron - 21 Mar 2006 21:10 GMT

> >> > Is there a way to set a primary index to ignore Nulls and if so how
> >> > would I do it. I have already tried setting it so it is not a required
[quoted text clipped - 27 lines]
>
> Could you be mistaken in your assertion?

I very well could be mistaken. It just appears wrong to create an index that
allows null.

I have created tables with numeric index fields where if I allowed 0, would
fail on any new records. I assumed null would do the same.

Reply to this Message

Anthony England - 21 Mar 2006 21:25 GMT

>> >> > Is there a way to set a primary index to ignore Nulls and if so how
>> >> > would I do it. I have already tried setting it so it is not a
[quoted text clipped - 38 lines]
> would
> fail on any new records. I assumed null would do the same.

I guess Lyle's point that null does not equal null is another way to look at
it.
In that sense, we haven't got two equal values in the field.

Reply to this Message

Lyle Fairfield - 21 Mar 2006 17:23 GMT

> (No Duplicates) could fail if more then one record had Null in the
> field. Also any new records after the 1st Null would require a value
> for the insert to succeed.

Null does not equal null.
Why would no duplicates preclude multiple nulls?

Signature

Lyle Fairfield

Reply to this Message