2

Two fields, Building and Room, make up a unique primary composite key in my rooms table. The key validates and saves, etc.

I have a BLANK Objects table which has three fields which will make it unique (again a composite primary key). The tables are as follows:

ROOM TABLE
[Building]     [Room]
01             101A
01             102
02             101A

OBJECT TABLE
[Building]     [Room]     [Number]
01             101A       1
01             101A       2
01             102        1
02             101A       1

How do I enforce referential integrity? When editing the relationships in MS Access' relationship tool, I get the following error: No unique index found for the referenced field of the primary table. I know (by trying non-unique values) that the composite keys for the primary (Object) table is correct. What am I doing wrong? How do I set up the proper relationships and maintain integrity (as updates will be a gruelling challenge without them)?

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37

1 Answers1

3

You need to set up your key like so:

Composite key

Note that the primary key for rooms is set to Building + Room and for Objects it is Building + Room + Numb (Number is a reserved word AFAIR)

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • 2
    Oh wow.... (you also replied to a previous, somewhat related question today because I couldn't get this method to work). Turns out that it matters which table is the "Related" table. I was dragging Building from objects to rooms instead of from rooms to objects. It makes sense.. but it didn't click with me that the Rooms table should be the "primary" table instead until I saw your screenshot. Solved! – StuckAtWork May 25 '12 at 18:23