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 |
#11
|
|||
|
|||
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.) |
#12
|
|||
|
|||
Unique index not recognizing null
"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.) |
#13
|
|||
|
|||
Unique index not recognizing null
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.) |
#14
|
|||
|
|||
Unique index not recognizing null
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 |
#15
|
|||
|
|||
Unique index not recognizing null
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 |
#16
|
|||
|
|||
Unique index not recognizing null
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. |
#17
|
|||
|
|||
Unique index not recognizing null
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! |
#18
|
|||
|
|||
Unique index not recognizing null
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. |
|
Thread Tools | |
Display Modes | |
|
|