Access: Composite Unique Index ignores NULLs

1
votes

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:

  • Creating a Validation Rule that would check for the uniqueness through the COUNT-function.

    • Access: "You shall not be able to use Aggregate Functions in your Validation rules."

  • Creating a Unique Index with the NZ-function, which would check for NULLs.

    • Access: "You shall not be able to use any sort of function in your index."

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

    • A pain to maintain and bad practice.

  • Insert an additional column that concatenates each of the 3 columns and create UNIQUE index that would only check the new column.

    • Redundancies and bad practice.

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?

3
databasems-accessindexingdatabase-designms-access-2010
I had pretty much the same problem a couple of weeks ago and ended up creating the extra table so wouldn't be any NULLS. stackoverflow.com/questions/39043760/… – Darren Bartrup-Cook
Don't try to make an index do the work that a constraint does. Indexes are for rapid access. Constraints are for rejecting non conforming data. – Walter Mitty
@Darren Thanks for relating your own question! There really doesn't seem to be better options for the problem. – BoSheq Milkman
@Walter Afaik, the unique "constraint" in Access is an index. Your lesson on Database 101 sadly won't change that fact :) – BoSheq Milkman
That depends on what the meaning of the word "is" is. An engine, such as Access, may use an index to partly implement a constraint. For example, an index is created when a primary key is declared. But the constraint itself involves some logic in the engine as well as the index. – Walter Mitty

3 Answers

1
votes

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.

0
votes

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.

0
votes

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.