Yes/No column with at most one yes

Question

I have a table which contains different versions of objects, e.g. Object A version 1, A version 2, B version 24... etc. One column stores the foreign key to the object, another stores the version number. It it obvious that these in combination should be unique and that is easy to implement with a unique index on both.

However, I want to be able to keep track of which version is the current one with an IsCurrent Yes/No column. The current version is not necessarily the one with the highest number. The problem here is that there is no way to define an index which is unique for yes values but allows many nos.

I find a lot of results when searching for this problem but none of them appear to work in Access. I have tried a "hack" in which I create a calculated column to use in a unique index which is -1 if current is true and the PK otherwise, but Access does not allow you to index calculated columns.

Is there any way to do this?

Answer 1

There is a trick, but you must allow only "yes/null" values for the isCurrent column - "yes" means "this row is current", and "null" otherwise.
This can be done using a validation check [isCurrent]="yes" Or [isCurrent] Is Null
Then create a composite + unique + ignore nulls index on id+isCurrent fields, and allow nulls.

Just click on the "index" button and define it in this way:

This prevents from inserting two rows with the same id + "yes" in the 'isCurrent' column, but allows many rows with the same id + null in the 'isCurrent' column.