CODEWITHSUNDEEP
sqlms-accessms-access-2010
JaredL
JaredL

Reputation: 1453

Yes/No column with at most one yes

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?

Upvotes: 1

Views: 660

Answers (1)

krokodilko
krokodilko

Reputation: 36107

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: enter image description here

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.

Upvotes: 3

Related Questions