Composite-key Primary Keys ==>> Foreign Keys

[Michael]

Hi Folks - Not too familiar with using composite keys as a primary key ...
If I have a 2 field composite key in a table, say lastname and firstname, do
I need to have both fields in a related table as foreign keys? Here's the
problem I have ... When I try to relate the lastname field in the
relationships window, no problem. But, when I try to relate the firstname
field, a message displays that says:

A relationship already exists, do you want to create a new relationship? If
I create a new relationship, then the relationships window displays the
relationship to a copy of the original table.

I figure I'm doing something wromg ....

Note: I know having a lastname and firstname is a bad choice for a composite
key. I'm using this as an example of my problem.

Thanks.

 

[RoyVidar]

Hi Folks - Not too familiar with using composite keys as a primary key ...
If I have a 2 field composite key in a table, say lastname and firstname, do
I need to have both fields in a related table as foreign keys? Here's the
problem I have ... When I try to relate the lastname field in the
relationships window, no problem. But, when I try to relate the firstname
field, a message displays that says:

A relationship already exists, do you want to create a new relationship? If
I create a new relationship, then the relationships window displays the
relationship to a copy of the original table.

I figure I'm doing something wromg ....

Note: I know having a lastname and firstname is a bad choice for a composite
key. I'm using this as an example of my problem.

Thanks.

Yes, lastname is a bad candidate for primary key.

If you're using a composite primary key, and you have a referencing
table (child table), you will need either the entire primary key as
foreign key, or you could for instance add a field to the referenced
table (parent table), give it a unique index, and use that for the
relationship (for instance an autonumber).

To "drag-n-drop" relationship in the relationships window, select all
the fields of the referenced table (parent table), then drag-em over to
the referencing table (child table). In the Edit Relationship dialog,
ensure all of the fields listed on the left side, matches a field in the
list for the related table/query.

Or, when you've created a relationship for one field, in the Edit
Relationship dialog, use the dropdowns below the alredy selected
field to select all fields within the composite primary/foreign key.

 

[John W. Vinson]

Hi Folks - Not too familiar with using composite keys as a primary key ...
If I have a 2 field composite key in a table, say lastname and firstname, do
I need to have both fields in a related table as foreign keys?
Yes.

Here's the
problem I have ... When I try to relate the lastname field in the
relationships window, no problem. But, when I try to relate the firstname
field, a message displays that says:

A relationship already exists, do you want to create a new relationship? If
I create a new relationship, then the relationships window displays the
relationship to a copy of the original table.

I figure I'm doing something wromg ....

Be sure that the Primary Key of the "one" side table is in fact a composite,
with the key icon by each field.

Remove all relationships (by selecting the join line or lines and deleting,
not deleting the table icon). Then, drag the first field of the composite key
to the corresponding foreign key field; then do the same with the second (and
third, and tenth if it comes to that). After you have all the lines drawn,
select each line in turn and choose "Enforce Referential Integrity" and, if
desired, cascade updates and/or cascade deletes.

John W. Vinson [MVP]

 

[Michael]

Roy - Excellent ... Thanks!!!!

Yes, lastname is a bad candidate for primary key.

If you're using a composite primary key, and you have a referencing
table (child table), you will need either the entire primary key as
foreign key, or you could for instance add a field to the referenced
table (parent table), give it a unique index, and use that for the
relationship (for instance an autonumber).

To "drag-n-drop" relationship in the relationships window, select all
the fields of the referenced table (parent table), then drag-em over to
the referencing table (child table). In the Edit Relationship dialog,
ensure all of the fields listed on the left side, matches a field in the
list for the related table/query.

Or, when you've created a relationship for one field, in the Edit
Relationship dialog, use the dropdowns below the alredy selected
field to select all fields within the composite primary/foreign key.

 

[Michael]

John - Thanks ... But, the technique you describe is what creates the
problem for me. What I needed to do is drag the first field of the composite
key onto the first field of the foreign key, then in the relationship
dialog, set all the other fields. So, in essence, I only needed to drag
once.

Michael

 

[Michael]

Revision .... If I drag the first field, no problem. When I drag the second
field, I am prompted if I want to edit the relationship. I need to say yes.
Then, in the relationships dialog, I need to ADD the second field to the
list of defined relationships. I get it ..... Thanks.

Michael