If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Unique index not recognizing null
"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! |
#3
|
|||
|
|||
Unique index not recognizing null
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. |
#4
|
|||
|
|||
Unique index not recognizing null
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. |
#5
|
|||
|
|||
Unique index not recognizing null
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. |
#6
|
|||
|
|||
Unique index not recognizing null
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. |
#7
|
|||
|
|||
Unique index not recognizing null
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. |
#8
|
|||
|
|||
Unique index not recognizing null
"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. |
#9
|
|||
|
|||
Unique index not recognizing null
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. |
#10
|
|||
|
|||
Unique index not recognizing null
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. |
|
Thread Tools | |
Display Modes | |
|
|