Relationship question

[Thwarted]

Can anyone tell me - is it possible to have a relationship between two tables when the primary key for one of those tables is made up of multiple fields?

Thanks!!

 

[LittleSmudge]

Yes.

All that need to happen is that the Foreign key in the other table is made up of the same set of fields.

Eg

tblMain
MainFirstId Joint Prime Key 1
MainSecondId Joint Prime Key 2
OtherField1
OtherField2
etc .. ..


tblManyEnd
ManyEndId Single Prime Key
MainFirstRef Joint Foreign Key 1
MainSecondRef Joint Foreign Key 2
ManyDataField1
ManyDataField2
etc .. ..



'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[fneily]

I must disagree with the above. Let's say your Primary table has a multi-field primary key. A related table can connect to any part of that primary key. So in the related table , only part of the primary key to the main table is needed.

Neil

 

[LittleSmudge]

Yes fneily, you CAN do that, in the same way as you can set up a relationship between any two fields ( of the same type ).

HOWEVER, what you CANNOT do with what you suggest is establish referential integrity between those fields because you do not have a 'unique index' if you are only using part of a complex Primary Key.


Horses for courses - as usual - but I'd suggest that Thwarted will find more use for my solution than for your.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[Thwarted]

Thank you both for your help.
I appreciate your time and will see what i can do.

T

 

[fneily]

To LittleSmudge
You're correct. But he didn't ask about ref. int. etc. Also my way would cause many to many connections. But maybe he wants that. I don't read minds as some people seem to do.

 

[Thwarted]

Sorry -
Didnt mean to cause a dispute. Maybe I can tell you what i have...
1 table that shows activities performed so for example
Name, Date, Time, Activity 1 Detail, Activity 2 Detail

Now Name, Date & Time combined represent 1 'group'. so Joe on Oct 28 at 2:15 did the following.. and there could be 1 or there could be 1000 records. I need to pull out
Joe, Oct 28th, 2:15 and then show ALL the records that were completed at that time by that guy. Does that make sense?

I know ideally this needs to be better normalized - but is there a way to do this as is?
Thanks a bunch
T

 

[LittleSmudge]

Okay Thwarted.

Don't mind fneily & I. We're just keeping each other professional, honest and accurate !


As for your problem
Well, Date and Time COULD be one field not two.

But your description of the rest does sound like you are looking for a joint Prime key and that you need a Joint Foreign Key in the other table to link to it.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D