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.


--
Michael Laferriere
HomeKey
401-921-2450
www.homekeyinc.com

Michael wrote:
> 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.

--
Roy-Vidar

On Tue, 6 Nov 2007 17:13:22 -0500, "Michael" <(E-Mail Removed)> wrote:

>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]

Roy - Excellent ... Thanks!!!!



"RoyVidar" <(E-Mail Removed)> wrote in message
news:4730ec0c$0$13724$(E-Mail Removed)...
> Michael wrote:
>> 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.
>
> --
> Roy-Vidar

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


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Tue, 6 Nov 2007 17:13:22 -0500, "Michael" <(E-Mail Removed)> wrote:
>
>>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]

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



"Michael" <(E-Mail Removed)> wrote in message
news:WmhYi.2113$(E-Mail Removed)...
> 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
>
>
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
> news:(E-Mail Removed)...
>> On Tue, 6 Nov 2007 17:13:22 -0500, "Michael" <(E-Mail Removed)> wrote:
>>
>>>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]
>
>