CODEWITHSUNDEEP
databasems-accessindexingdatabase-designms-access-2010
BoSheq Milkman
BoSheq Milkman

Reputation: 23

Access: Composite Unique Index ignores NULLs

Consider this table structure:

CREATE TABLE [TableA]
(
    [PK_ID] int NOT NULL PRIMARY KEY,
    [Name] text NOT NULL,
    [FK_TableB] int NULL,
    [FK_TableC] int NULL,
    [Value] single NULL
)

I would like to create a unique index over Name, FK_TableB and FK_TableC, so that the data in those 3 columns remains unique. Sadly, the 2 FK columns are nullable and Access automatically ignores NULLs in UNIQUE indexes, making this possible:

Name      | FK_TableB | FK_TableC
----------+-----------+-----------
Text1     | NULL      | NULL
Text1     | NULL      | NULL

I respect Access for having its own believes about whether NULL is a checkable value, but in this case it's incredibly contraproductive. Creating such an index in SQL Server works perfectly well, and I would love to find a way in Access.

This is what I've tried (and failed) / considered so far:

My colleague is considering creating a Table for each possible case:

  1. Non-nullable unique Name
  2. Non-nullable unique Name and FK_TableB
  3. Non-nullable unique Name, FK_TableB and FK_TableC

Is this the only solution I've got?

Upvotes: 2

Views: 1015

Answers (4)

Combinatix
Combinatix

Reputation: 1216

You can use Zero Length Strings instead of NULLs

Set these properties for your columns:

Allow Zero Length = Yes
Default Value = ""
Required = Yes

The Access will let you to add only one empty string. You have to make sure to convert the NULLS to "" and vice versa in your code, but that's rather easy

I personally don't like storing empty strings but in this case it can do the trick.

EDIT: There is a bug in MS Access that you should be aware of when using ZLS - see the Allen Browne's blog: http://allenbrowne.com/bug-09.html

Upvotes: 0

Mathieu Trentesaux
Mathieu Trentesaux

Reputation: 41

There is a solution without coding.

This is a broken feature in the Jet engine. The "Ignore Nulls" flag in the index definition should have this effect, it's a failure in my opinion. What I haven't checked is that I have the impression that it was doing the right thing in an older version of DAO. I have had several times in my career to index in such a way (Name and Surname + Deduplication) where 'Deduplication' is almost always empty.

The index does not work with the value Null, but does work with the value Empty (""). Set the second field to 'Null forbidden' and 'Empty string allowed' and, as a solution, set "" to its default value.

When a new record is inserted, the second field will be set to Empty and the index will either insert or not, as expected. An update of the field will leave or give back the 'emptyness' to the second field.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

Indexes ignore NULL in a lot of SQL databases, in order to side step the paradoxes that arise with three valued logic. The result is that the unique index isn't constraining the data in exactly the way you want to.

Here's what I mean by three valued logic. When NULL is tested for equality with NULL, the result is neither TRUE nor FALSE, but a third logical value UNKNOWN. If this seems like an ugly can of worms, that's because it is.

You are taking NULL in an FK to not only fail to provide a value for the link to the reference, but also to assert that an optional relationship does not exist in this instance. That's a reasonable interpretation, but it isn't the one that index builders typically use. Incidentally, you would run into exactly the same problem with just about any SQL database.

The solution? Well, you could normalize your data into Sixth Normal Form, a form that is often omitted from normalization discussions. In 6NF, every nullable column results in a decomposition into two tables, one of which includes the nullable column, and one that doesn't. The result is that every column can be flagged as non nullable, without loss of expressive power.

This will get you around the problem you've stated, but it may well be more trouble than it's worth.

Upvotes: 0

Andre
Andre

Reputation: 27644

Make the FK-Fields required and insert a default / NULL-like record to each of the related tables.

This would be my preferred solution, with FKs 0.

I don't see how it's a pain to maintain - just insert the records in the related tables once, change the FK Default from NULL to 0, and be done with it.

You may need to change some of your application logic (where you now test for FK_TableB IS NOT NULL, you then do FK_TableB > 0).

IMHO it's also not bad practice, it's better than nullable FKs.

It saves you from doing lots of OUTER JOINs - they can create problems like non-editable query results or bad performance.

Upvotes: 1

Related Questions