Unique index not recognizing null

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.

--
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.

"Flavelle Ballem" wrote in message
...
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.

"Flavelle Ballem" wrote:

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
"Allen Browne" wrote:

"Jerry Whittle" wrote in
message
...
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.)

"Allen Browne" wrote:

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.

--
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.

"Flavelle Ballem" wrote in message
...
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.

"Flavelle Ballem" wrote:

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
"Allen Browne" wrote:

"Jerry Whittle" wrote in
message
...
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.)

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

"Allen Browne" wrote:

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.

--
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.

"Flavelle Ballem" wrote in message
...
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.

"Flavelle Ballem" wrote:

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
"Allen Browne" wrote:

"Jerry Whittle" wrote in
message
...
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.)

On Tue, 23 Sep 2008 00:56:01 -0700, Flavelle Ballem wrote:

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

"Allen Browne" wrote:

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.

--
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.

"Flavelle Ballem" wrote in message
...
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.

"Flavelle Ballem" wrote:

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
"Allen Browne" wrote:

"Jerry Whittle" wrote in
message
...
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.)



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

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

"Michael Gramelspacher" wrote:

On Tue, 23 Sep 2008 00:56:01 -0700, Flavelle Ballem wrote:

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

"Allen Browne" wrote:

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.

--
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.

"Flavelle Ballem" wrote in message
...
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.

"Flavelle Ballem" wrote:

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
"Allen Browne" wrote:

"Jerry Whittle" wrote in
message
...
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.)



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

On Tue, 23 Sep 2008 10:38:07 -0700, Flavelle Ballem wrote:

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.

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.

"Flavelle Ballem" wrote:

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!

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

"Michael Gramelspacher" wrote:

On Tue, 23 Sep 2008 10:38:07 -0700, Flavelle Ballem wrote:

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.