Unique index not recognizing null

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!

"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


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

On Wed, 20 Aug 2008 12:36:24 -0700, 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!

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.

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.

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

"Jeff Boyce" wrote in message
...
"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


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

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,




"Michael Gramelspacher" wrote:

On Wed, 20 Aug 2008 12:36:24 -0700, 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!

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.

On Thu, 21 Aug 2008 07:12:02 -0700, Flavelle Ballem wrote:

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,




"Michael Gramelspacher" wrote:

On Wed, 20 Aug 2008 12:36:24 -0700, 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!

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.


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.

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Michael Gramelspacher" wrote:

On Thu, 21 Aug 2008 07:12:02 -0700, Flavelle Ballem wrote:

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,




"Michael Gramelspacher" wrote:

On Wed, 20 Aug 2008 12:36:24 -0700, 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!

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.


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.

"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 - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

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 - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

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 - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.