Re: Table links and PKs

"CliveW" <clivew_at_clivewalden.com> wrote in message news:a7e7de55.0208061101.51be1569_at_posting.google.com...
> Design tools I have complain when I try to set up a table with a
> multi-field Primary Key (PK) with one field only linked as a Foreign
> Key (FK) to the PK of another table.
>
> However this configuration often seems natural to me. Can anyone
> explain?
>
> Example of how I might choose to use it is in a Contacts telephones
> table:
>
> CONTACTS
> -> Contact_Id PK
> | ...Other fields
> |
> | PHONES
> | PhoneNumber PK1
> |_ Contact_Id PK2
>
> As multiple contacts may share the same telephone number, PhoneNumber
> on its own is not an appropriate PK.
>
> Because my design tool complains I usually add a surrogate PK and put
> a unique index on the two fields above.
>
> Why is the above design perceived as flawed?
>
> Thanks,
> Clive.

If I revert your statement
> As multiple contacts may share the same telephone number, PhoneNumber
> on its own is not an appropriate PK.

you seem to say
'one contact can have only one phone'
If that would have been true phone number and contact id would have constituted a composite key.
However, as the phone number is unique on it's own, you can't have the contact id and the phone details in one table,as it would force you to repeat the phone number details. The way I look at it is there is a junction table hiding in your phone number table. You need 3 tables contact -> contactphone <- phone.
Using Designer you would have created a n:m relationship between entities and Designer would have created the junction table for you.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address