Setting up relational integrity with only one of two key fields

[Linda V]

I'm trying to set up referential integrity between two
tables. Here's what I have:

Table 1:
Primary key is made up of two fields:
Field 1: Month *
Field 2: Day *
* Not actual field name

Table 2 also has a Month field but it doesn't have the Day
field. All it cares about is Month.

In Table 1 any particular Month and Day can be listed only
once, but as you can guess Month can be listed many
times. And in Table 2 there can be multiple records with
the same Month.

I'd like to set up integrity between just the Month fields
but keep getting this error "No unique index found for the
referenced field of the primary table."

Can anyone tell me what I need to do?

Thanks!

Linda

 

[Ken Snell [MVP]]

Can't do this in the table (relationship window) itself. You'll need to have
your database's programming maintain this for you.

 

[Tim Ferguson]

I'd like to set up integrity between just the Month fields
but keep getting this error "No unique index found for the
referenced field of the primary table."

Can anyone tell me what I need to do?

Yes: you need a Unique Index (usually the Primary Key) for the foreign key
to point to. Therefore, Table2.Month must be at least uniquely indexed, and
there is usually no good reason not to use the existing PK. Whether the
best PK is indeed Month or some other field or fields we cannot tell, since
names like "Table2" do not really impart much about the meaning of the
thing.

Remember that db design is above all about semantics, trying to abstract it
really doesn't help -- once you take away the meaning from the objects you
are trying to model, you do not leave enough behind to learn anything.

What are you actually trying to model here?


Tim F

 

[Linda V]

What am I actually trying to model? I'm modeling
consumers on a given piece of conductor (the electric
wires you see on telephone poles, and those you don't see
underground).

In the real world, a length of conductor can span multiple
poles so it can be hundreds of feet long. It can have
many consumers tied to it.

However, in my world, we're creating digital maps and
whenever a conductor crosses a map edge we have to, in
essence, 'break' the conductor into multiple pieces. If
it crosses one map edge you get two pieces. If it crosses
two map edges you get 3 pieces, etc. So in my database I
could have conductor '100' that might consist of three
different pieces. Hence the two field primary key:
conductor number ('100') and piece (1, 2, or 3). (Please
don't suggest that we change the way we're creating the
digitial maps. That is out of the question right now.)

Given what we have, I'd like to set up referential
integrity between my consumers and my conductors. As I
said in my initial email, the consumers know their
conductor but they don't know, or care, about the piece.

I envision referential integrity between the two working
as such: a consumer can't be assigned to a conductor
unless that conductor exists. It doesn't matter if I have
all the pieces there. If there's one piece there then it
exists.

Likewise, a conductor cannot be totally deleted unless
there are no consumers tied to it. By "totally" I mean
all pieces. So if I have three pieces, two of the pieces
can be deleted without problem, but if I try to delete the
last piece and there are consumers tied to it I should get
an error.

My two tables are Conductor and Consumer.

Conductor table - Primary key is Conductor number + Piece

Consumer table - Primary key is Consumer number. This
table also contains a Conductor number field.

According to Access Help I have to create a junction table
for this relationship. I did that. It contains three
fields; Conductor number, Piece and Consumer number. I
then created a primary key using all three fields and set
up referential integrity using the junction table. Using
the junction table I can set up referential integrity
without getting the error I talked about in my intial
email. So all looked well.

However, to test it I went into my Consumer table and
tried to add a consumer with a conductor number not
specified in my Conductor table, and it let me. No
error. So it still isn't enforcing referential integrity.

I saw Ken's response that this has to be handled
programatically. I was hoping that wasn't true, but it's
looking like it is...unless you have some tricks up your
sleeve we don't know about. Any tricks?

Thanks, Tim.

Linda

 

[Tim Ferguson]

Hello Linda

My two tables are Conductor and Consumer.

I detect that you are actually describing three different entities, though.
Pieces and Conductors are not the same thing, and each should be modelled
by a table.

Conductor table - Primary key is Conductor number + Piece

This is what I mean: a single Conductor has lots of Pieces, so there should
be two tables --

Conductors(*ConductorNumber, Wattage, StartsFrom, ... etc)

Pieces(*ConductorNumber(FK), *PieceCode, StartMap, EndMap, ... etc)

These two are clearly in a one-to-many relationship. You said above

As I
said in my initial email, the consumers know their
conductor but they don't know, or care, about the piece.

(actually the initial mail talked about Months and Years, but never mind!)

If _you_ care about which Piece the Consumer is connected to, then the
relationship should be between Consumers(ConductorID, PieceCode) and
Pieces. Otherwise, enforcing the relationship between Consumers and
Conductors is fine.

Consumer table - Primary key is Consumer number. This
table also contains a Conductor number field.

Yes, that is fine.

Likewise, a conductor cannot be totally deleted unless
there are no consumers tied to it. By "totally" I mean
all pieces. So if I have three pieces, two of the pieces
can be deleted without problem, but if I try to delete the
last piece and there are consumers tied to it I should get
an error.

Unfortunately, Access cannot do this for you, with the model you described
above. A "real" RDBMS has triggers that can check this kind of thing, but
not this one, I'm afraid. You will have to control this behind a form, and
make sure that nobody had access to table datasheets, or making their own
queries, etc.

If you do relate Consumers to Pieces to Conductors, though, this rule will
be enforced by normal FK integrity.

Hope that helps



Tim F