RE: Unique index not recognizing null

Tech-Archive recommends: Speed Up your PC by fixing your registry

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!

.

Relevant Pages

  • Errors In Dapper
    ... First set of errors or as follows... ... W: Duplicate sources.list entry http://ca.archive.ubuntu.com dapper/main ...
    (Ubuntu)
  • Re: Error in the terminal trying to fix an error with synaptic Manager
    ... W: Duplicate sources.list entry http://security.ubuntu.com edgy-security/restricted Packages ... by that error message that Synaptic gives you. ...
    (Ubuntu)
  • Re: Duplicate Record with .ADDNEW
    ... rsOrderItems.open "tblOrderItems", ConnOrder, adOpenStatic, ... no issue where as on production server of SQL Server it is causing ... I beleive .ADDNEW method is creating duplicate entries on ... I have following code which creates new entry in table ...
    (microsoft.public.vb.database.ado)
  • Re: Duplicate Entry in MSCONFIG
    ... an orphaned and inactive entry that was ... produced by msconfig. ... duplicate item. ...
    (microsoft.public.windowsxp.general)
  • Re: Oracle Toplink
    ... Are there fields besides the primary key with a UNIQUE constraint? ... When you say "this entry" "is not in database", did you check to ensure that the object's values are distinct for every UNIQUE column in the table? ... The "entry" will never be in the database - a *row* will be in a table and that row's *columns* will have values, some of which might conflict with the values you are trying to insert. ...
    (comp.lang.java.programmer)