Unique index not recognizing null

F

Flavelle Ballem

I am probably missing something, and it's easier to explain by example.

In Acess 2003:
1. Create a table with three fields (I've named it Table1):
Field: ID Autonumber, Primary Key
Field: Field1, text, Required = No, no index
Field: Field3, long integer, Required = Yes, no index
2. Create an index for the table (I've named it ux_Field1_Field2):
Includes Field1 and Field2
Primary = No
Unique = Yes
Ignore Nulls = No

3. Add the following entries to the table:
Entry 1: Field1 = 'Text Entry 1', Field2 = 1
Entry 2: Field1 is null, Field2 = 2

My objective in setting up the index in the way that I did was to ensure
that if I were to attempt to add another entry like either Entry 1 or Entry
2, the add would not succeed.

Actual result:
If I attempt to add a second entry with the identical contents to Entry
1, the add fails - duplicate index.
If I attempt to add a second, third, fourth ... entry with the identical
contents to Entry 2, the add succeeds every time. It should fail.

Could someone let me know if this is 'fixable'. I cannot change the
assignment of the Primary key, since it is important the Field 1 allow for
null values.

Thanks!
 
J

Jeff Boyce

"Null" means "undefined". You won't be able to compare one null with
another.

It looks like you told Access to NOT "Ignore Nulls".

If you try to add "another entry like entry 2", you'll be comparing Null to
Null (these are undefined and are NOT the same).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Michael Gramelspacher

I am probably missing something, and it's easier to explain by example.

In Acess 2003:
1. Create a table with three fields (I've named it Table1):
Field: ID Autonumber, Primary Key
Field: Field1, text, Required = No, no index
Field: Field3, long integer, Required = Yes, no index
2. Create an index for the table (I've named it ux_Field1_Field2):
Includes Field1 and Field2
Primary = No
Unique = Yes
Ignore Nulls = No

3. Add the following entries to the table:
Entry 1: Field1 = 'Text Entry 1', Field2 = 1
Entry 2: Field1 is null, Field2 = 2

My objective in setting up the index in the way that I did was to ensure
that if I were to attempt to add another entry like either Entry 1 or Entry
2, the add would not succeed.

Actual result:
If I attempt to add a second entry with the identical contents to Entry
1, the add fails - duplicate index.
If I attempt to add a second, third, fourth ... entry with the identical
contents to Entry 2, the add succeeds every time. It should fail.

Could someone let me know if this is 'fixable'. I cannot change the
assignment of the Primary key, since it is important the Field 1 allow for
null values.

Thanks!

I verson of Access with SP3 does the same thing. I do not think it used to work this way.

I tried this in SQL Server Express

my_key my_text my_int
----------- -------------------- -----------
4 NULL 1
5 NULL 2
2 hello NULL
1 hello 1

(4 row(s) affected)

I cannot duplicate any of these (my_text,my_int) combinations without getting an error.
In Access I can sit there all day long and enter hello, null and have it accept.

I remember once having a locations table with a unique index on (city,state) and
with entries like:

Troy MI
Troy IN
Troy null

I got an error trying to enter another Troy null

Maybe I am wrong -- happens all the time, but I think Access and SQL Server used to work alike in
this regard.
 
A

Allen Browne

Jeff explained why it works as it does.

If you are trying to create a unique index on the combination of Field1 +
Field2, you could use these properties for Field1:
Required Yes
Allow Zero Length Yes
Default Value ""
This will allow at most one entry where Field1 contains a zero-length string
for any value in Field2.
 
F

Flavelle Ballem

Michael:

Your recollection, and mine, are identical. In a compound unique key where
one or more of the fields is null, and ignore nulls is set to no, there
should be no way to add more than one identical entry - that's the purpose
behind ignore nulls. In some circumstances, null is an acceptable value.

As for Jeff's request for an example:

1. Table1 contains the following fields:
Field: ID, Autonumber, Primary Key
Field: Field1, text, Required = Yes, no index
Field: Field2, long integer, Required = No, no index
2. Index: ux_Field1_Field2:
Includes: Field1 and Field2
Primary = No
Unique = Yes
Ignore Nulls = No

This is slightly different than my original example.

If Field2 is a Foreign Key, then if there is no entry in the primary table,
Field2 should be null. In my specific case, Field2 is a self-join - it would
identify the parent for the current record. If there is no parent, then
Field2 must be null. The objective for putting the unique key in is to ensure
that there is a maximum of one occurrence of a value for Field1 for each
occurrence of Field2, including those where there is no parent (ie. Field2 is
null).

If Field2 were text, then I could use the empty string as a workaround, but
it is numeric. If I put zero in, then it must exist, and autonumber does not
allow for zero.

Hope this clarifies the question, and the problem. There appears to be a bug,
 
M

Michael Gramelspacher

Michael:

Your recollection, and mine, are identical. In a compound unique key where
one or more of the fields is null, and ignore nulls is set to no, there
should be no way to add more than one identical entry - that's the purpose
behind ignore nulls. In some circumstances, null is an acceptable value.

As for Jeff's request for an example:

1. Table1 contains the following fields:
Field: ID, Autonumber, Primary Key
Field: Field1, text, Required = Yes, no index
Field: Field2, long integer, Required = No, no index
2. Index: ux_Field1_Field2:
Includes: Field1 and Field2
Primary = No
Unique = Yes
Ignore Nulls = No

This is slightly different than my original example.

If Field2 is a Foreign Key, then if there is no entry in the primary table,
Field2 should be null. In my specific case, Field2 is a self-join - it would
identify the parent for the current record. If there is no parent, then
Field2 must be null. The objective for putting the unique key in is to ensure
that there is a maximum of one occurrence of a value for Field1 for each
occurrence of Field2, including those where there is no parent (ie. Field2 is
null).

If Field2 were text, then I could use the empty string as a workaround, but
it is numeric. If I put zero in, then it must exist, and autonumber does not
allow for zero.

Hope this clarifies the question, and the problem. There appears to be a bug,

I ran this in SQL Server Express. The first five inserts are ok, the last three reject. Run the
same thing in Access and you end up with eight rows.

CREATE TABLE MyTable (
my_key INTEGER IDENTITY NOT NULL PRIMARY KEY,
my_text VARCHAR (20) NULL,
my_integer INTEGER NULL,
UNIQUE (my_text,my_integer));

INSERT INTO MyTable (my_text,my_integer) VALUES ('hello',2);
INSERT INTO MyTable (my_text,my_integer) VALUES ('world',null);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,2);
INSERT INTO MyTable (my_text,my_integer) VALUES ('world',2);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,null);

INSERT INTO MyTable (my_text,my_integer) VALUES ('world',null);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,2);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,null);

from SQL Server help:
"Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with
any value participating in a UNIQUE constraint, only one null value is allowed per column."

Maybe the way Access handles Null in Unique constraints is not compliant with ANSI. Really though, I
yield to the experts in times like this. I just do not know.
 
J

Jerry Whittle

I just tested it on Access 2007 and it allowed multiple records. The Ignore
Nulls setting made no difference.

I created a similar senario in Oracle 10g and it cause an "ORA-00001: unique
constraint (WHITTLEJ.TBLNULLCONSTRAINT_U01) violated" error.
 
A

Allen Browne

Jerry Whittle said:
I just tested it on Access 2007 and it allowed multiple records. The Ignore
Nulls setting made no difference.

That's correct. Ignore Nulls does *not* mean block nulls.
It means, Don't keep an index pointer for records that are null.

From the A2003 VBA help file on the IgnoreNulls property:

<quote>
Remarks:
You can define an index for a field to facilitate faster searches for
records indexed on that field. If you allow Null entries in the indexed
field and expect to have many of them, set the Ignore Nulls property for the
index to Yes to reduce the amount of storage space that the index uses.
</quote>

Hence my previous advice to set the fields' Required property to Yes, and
allow ZLS (which is a unique value.)
 
F

Flavelle Ballem

For reasons explained in my prior post (second example), the field that
allows a null value is a number, not a string, so ZLS will not work.

From what I gather from other posters, the Ignore Nulls setting in the index
is not doing what it is supposed to do:

from: http://msdn.microsoft.com/en-us/library/bb177891.aspx, which describes
the CreateIndex statement

In the optional WITH clause you can enforce data validation rules. You can:
....
- Prevent records with Null values in the indexed field or fields from being
included in the index by using the IGNORE NULL option.
....

In other words, if IGNORE NULL is true, then any record that has a null
value in one or more of the key fields will not be included in the index -
thus allowing for multiple records that contain a Null value in one of the
key fields to be included.

If IGNORE NULL is false, then all records, including those that have a null
value in one or more of the key fields will be included in the index and will
be subject to any constraints, including uniqueness.

I do remember at one point in time that the Access index worked correctly. I
know that it is now not working correctly and would appreciate any guidance
on how to report the bug.

Thanks
 
F

Flavelle Ballem

I was hoping that there would be a follow-up to this, since the general
consensus is that the null values should be included in the index (and
therefore subject to a unique constraint) and that it used to work this way,
and does work this way in other database products, including SQL Server.
 
A

Allen Browne

Flavelle, I don't think there's anything to add.

The concept of a 'unique null' is an oxymoron. Null is not equal to
anything. One unknown value is not the same as another unknown value.

Clearly, Access is not working as you expect, but it is working consistently
and as documented.
 
F

Flavelle Ballem

Allen Browne said:
Flavelle, I don't think there's anything to add.

The concept of a 'unique null' is an oxymoron. Null is not equal to
anything. One unknown value is not the same as another unknown value.

Clearly, Access is not working as you expect, but it is working consistently
and as documented.
 
F

Flavelle Ballem

Allen:

With respect, I disagree, and if I am reading other posters correctly, I'm
pretty sure that they would disagree. The behaviour of 'ignore null', when
applied to a key that includes a unique constraint, is not correct, not
consistent with other products - including SQL Server, Oracle, and how MS
Access used to behave, and is not consistent with the documentation.

To summarise, when ignore null is 'yes', then any record with null in any
key field will not be included in the index - therefore not subject to any
constraints of the index. When ignore null is 'no', then all records,
including those with null in the key field(s), will be included in the index,
and are subject to the constraints of the index (including uniqueness). That
is the behaviour that is correct, consistent with other products - including
SQL Server, Oracle, and how MS Access used to behave, and is consistent with
the documentation.

I would be most grateful if you could please give me detailed instructions
on how to file this bug so that it can be corrected.

Many thanks,
Flavelle
 
M

Michael Gramelspacher

Allen:

With respect, I disagree, and if I am reading other posters correctly, I'm
pretty sure that they would disagree. The behaviour of 'ignore null', when
applied to a key that includes a unique constraint, is not correct, not
consistent with other products - including SQL Server, Oracle, and how MS
Access used to behave, and is not consistent with the documentation.

To summarise, when ignore null is 'yes', then any record with null in any
key field will not be included in the index - therefore not subject to any
constraints of the index. When ignore null is 'no', then all records,
including those with null in the key field(s), will be included in the index,
and are subject to the constraints of the index (including uniqueness). That
is the behaviour that is correct, consistent with other products - including
SQL Server, Oracle, and how MS Access used to behave, and is consistent with
the documentation.

I would be most grateful if you could please give me detailed instructions
on how to file this bug so that it can be corrected.

Many thanks,
Flavelle

After some researching, I learned that Access and SQL Server work differently, and it is actually
Access that is accordance with the standard.

CREATE TABLE MyTable (
my_key INTEGER IDENTITY NOT NULL PRIMARY KEY,
my_text VARCHAR (20) NULL,
my_integer INTEGER NULL,
UNIQUE (my_text,my_integer));

INSERT INTO MyTable (my_text,my_integer) VALUES ('hello',2);
INSERT INTO MyTable (my_text,my_integer) VALUES ('world',null);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,2);
INSERT INTO MyTable (my_text,my_integer) VALUES ('world',2);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,null);

INSERT INTO MyTable (my_text,my_integer) VALUES ('world',null);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,2);
INSERT INTO MyTable (my_text,my_integer) VALUES (null,null);

SELECT * FROM MyTable;

In SQL Server the last three inserts will reject as being duplicate.

Try this in Access and you will get eight rows.

Sub testnull()

With CurrentProject.Connection

.Execute "CREATE TABLE MyTable (" & _
"my_key INTEGER IDENTITY NOT NULL PRIMARY KEY," & _
"my_text VARCHAR (20) NULL," & _
"my_integer INTEGER NULL," & _
"UNIQUE (my_text,my_integer));"

.Execute "INSERT INTO MyTable (my_text,my_integer) " & _
"VALUES ('hello',2);"
.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES ('world',null);"
.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES (null,2);"
.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES ('world',2);"
.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES (null,null);"

.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES ('world',null);"
.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES (null,2);"
.Execute "INSERT INTO MyTable (my_text,my_integer)" & _
"VALUES (null,null);"

End With
End Sub
 
F

Flavelle Ballem

Michael:

Thanks for the feedback, but if Access is the standard, then that means that
everyone else (SQL Server, Oracle, etc.) got it wrong.

So my question is, "What is the value of including Null values in the Index
if they're not subject to the constraints of the Index?" I absolutely
understand the value of not including Null values in a unique index (there
are circumstances where this is exactly what is required), but if there
should only be one Null entry in the index, then this should be managed
through the constraint. If not, then how is it to be managed?

Thanks,
Flavelle
 
M

Michael Gramelspacher

Michael:

Thanks for the feedback, but if Access is the standard, then that means that
everyone else (SQL Server, Oracle, etc.) got it wrong.

So my question is, "What is the value of including Null values in the Index
if they're not subject to the constraints of the Index?" I absolutely
understand the value of not including Null values in a unique index (there
are circumstances where this is exactly what is required), but if there
should only be one Null entry in the index, then this should be managed
through the constraint. If not, then how is it to be managed?

Thanks,
Flavelle


I am sorry that I really cannot answer your questions. Really all I did was some reading using
Google and found previous discussions of this issue. If I am not mistaken, some SQLServer experts
felt that SQL Server should be changed to handle nulls correctly. I gathered that other database
products handled null correctly. But you say that Orcale handles null in unique indexes same as SQL
Server. I have no knowledge of that.
 
F

Flavelle Ballem

In looking at all of the postings in this exchange, I have arrived at some
conclusions and I have a further question.

Conclusions:

1. In those cases where a unique index can include null in at least one of
the key fields, it is always possible to have two or more records that are
exact duplicates, if at least one of the key fields is Null.

2. If ignore null is False, then the duplicate records will still be
included in the index, but they will be duplicates and not subject to the
unique constraint. If ignore null is True, then the records will not be
included in the index.

3. This is how it is in Access, and apparently in the SQL Standard. SQL
Server and Oracle will honour the unique constraint if ignore null is false,
but Access will not honour the constraint.

4. Access is not about to change. I cannot file a bug report or a change
request, since I don't know how and no one seems inclined to tell me how.

Question:
How do I prevent duplicate records where one of the key fields may be null?
For example, in a unique index with two key fields (Field 1 and Field 2), how
do I prevent two records having the values "My value",NULL, which is the
requirement. Field 2, in this case, is not a Text field, so a Zero-length
String is not an option.

Any help to solve my problem would be very much appreciated.

Thanks,
Flavelle

Any help in this would be appreciated.
 
F

Flavelle Ballem

Michael:

Regarding Oracle, I refer you to Jerry Whittle's post earlier in this chain.
I have also worked with Oracle although I do not currently have access to
verify the behaviour. I do recall that null in a key field of an index was
subject to all constraints for the index, including uniqueness.

I appreciate your note regarding the standard. It strikes me as very strange
that Oracle and SQL Server got it wrong. I also recall at one time, MS Access
did enforce the constraint. I have used MS Access for a long time, and I am
very unpleasantly surprised that the constraint was not honoured in the case
of null in a key field.

Still, I do have a problem that needs a solution, which explains my post.
Any guidance to solve the problem would be very much appreciated.

Regards,
Flavelle
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top