Access 2007 one-to-two columns referential integrity

Question

Setup:
One table called documents with columns author_id and reviewer_id (among others).
One table called users with column user_id.

I need to create two referential integrity links:
One-to-many between user_id and author_id.
One-to-many between user_id and reviewer_id.

In other words, these two columns in documents are independent of one another, but both should have a value that is a valid user_id.

Adding either one of these relationships works fine. When I try to add a second relationship in the Edit Relationships window, Access complains with the following message:

Microsoft Office Access can't enforce referential integrity for this relationship.

I've searched, but couldn't find any solutions to this. Any ideas on how this relationship could be configured?

Answer 1

To add two individual relationships from one table to two different fields in another, you need to have multiple instances of the parent table in the relationship window.

So, you'd add your Users and Documents table to the relationships window and create the first relationship. Then add the Users table to the relationship window a second time (it will be aliased as Users_1), and then add the second relationship from this aliased copy.

This is completely consistent with the same way you'd define two such joins in the QBE, so I'd say it's not problematic at all. But it's not necessarily obvious!

Answer 2

This is not a direct answer to your question, but if I were you I would use another data model to implement the complex many-to-many relation between Documents and Users by creating a third table called DocumentsUsers with the following fields:

PK  documentUser_id (*)
FK  document_id
FK  user_id
    documentUser_type

(*) or you could alternatively use document_id + user_id as a PK...

The documentUser_type field will hold the relation type, ie 'user', 'reviewer', etc. By using this model, you could have, for the same book, multiple users and/or multiple reviewer, which might be closer to reality. You could even have other relation types like 'author', etc.

Answer 3

First, I suggest you change you columns' names to author_user_id and reviewer_user_id respectively, to make it clear that each reference user_id.

Second, you should be aware that use of Access's UI tools are not compulsory. Many of us find them unintuitive but happily there are alternatives. One is to use SQL DDL e.g. ANSI-92 Query Mode:

ALTER TABLE Documents ADD 
   CONSTRAINT fk__ document_author_user_id__Users
   FOREIGN KEY (author_user_id)
   REFERENCES Users (user_id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
;

ALTER TABLE Documents ADD 
   CONSTRAINT fk__ reviewer_user_id__Users
   FOREIGN KEY (reviewer_user_id)
   REFERENCES Users (user_id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
;

Third, consider you may need a CHECK constraint (or Table[sic] Validation Rule) to ensure a user cannot review their own work e.g.

ALTER TABLE Documents ADD 
   CONSTRAINT document_author_cannot_review_their_own_work
   CHECK (author_user_id <> reviewer_user_id)
;