I have an Access table called
Engagement with four fields:
Emp_id, Year, Week, Act_id
It records when an employee is/was engaged in an activity.
Each field is Number and each makes up part of the composite primary key.
The semantics of the application was that each entry (each
Act_id combination) must be unique.
That is, while an employee will generally have different year-weeks and different activities, an employee can sometimes engage in the same activity on different year-weeks or even engage in different activities in the same year-week. Nulls were not allowed. Everything worked.
Now I need to expand/modify the semantics to allow unknown – or more aptly, not disclosed – Year-Week values associated with any given employee-activity. Of course, attempting to enter a row with empty Year-Week results in "Index or primary key cannot contain a Null value". So I need a change to the table design.
One thing I tried was to convert the primary key index into a non-primary index by turning Primary off (and leaving Unique on) in the Indexes window. This correctly prevents duplicate records where Year-Week values are non-empty -- but it allows duplicate records where Year-Week are empty.
For example, using the above non-primary, unique index, the following data are allowed:
Emp_id Year Week Act_id 7 2014 12 31 } Same activity, 7 2015 22 31 } different dates. 7 2015 33 32 7 2015 40 33 } Same dates, 7 2015 40 34 } different activities. 7 2016 2 36 7 38 } Different activities, 7 39 } undisclosed dates.
And none of the following additions are subsequently allowed:
Emp_id Year Week Act_id 7 2014 12 31 } Both records are 7 2015 33 32 } duplicates of above.
So far so good (behavior matches requirements). However, both the following additions are subsequently allowed, but should not be:
Emp_id Year Week Act_id 7 38 } Both records are 7 39 } duplicates of above.
Why is that?
What is a good way around this problem of, on the one hand, allowing something equivalent to null for Year and Week and, on the other hand, constraining each
Act_id combination to be unique?
I can think of two other (untried) solutions:
- Choose an equivalent-to-null numeric value, such as zero or -1 and somehow explain this to users.
- Convert the Year-Week field types from Number to Text and simply use "" (empty string) for null.
In your experience, what is a good solution in this kind of situation?
I'm aware of What's wrong with nullable columns in composite primary keys? and NULL value in multi-column primary key, which explain certain things but don't provide a solution.