Solved Joining not primary key with enforcing referential integrity

[jaryszek]

Hi Guys,

I have relationships something like here:

And also i am using tblVolumes and there i have reference to serverClassID (i am not using relationships here because i have already used them in seperate case as above).
Now i got one new table --> tblServerDetails where i have also key "ServerClassID" and have to create relationships between tblVolumes and tblServerDetails.

I could do something like here:

but this is correct approach?
What do you do in this case? What is your solution?

Unfortunately i can not do "Enforce referiental itengrity" because of error:

How to do this correctly? Just model entirely new junction table with ServerClassID ? But how to relate it?

Please help,
Best wishes,
Jacek

 

[theDBguy]

Hi. If you have a many-to-many relationship between two tables, you need to create a junction table to break down their relationship into two one-to-many relationships, and you will be able to enforce RI with them.

 

[The_Doc_Man]

More correctly, data-field to data-field relationships cannot be DIRECTLY resolved... but a junction table acting as a go-between could do the job with only a little more effort.

 

[isladogs]

Agree with the above comments.

Jacek
Can I once again refer you to my article about relationships & RI:
http://www.mendipdatasystems.co.uk/relationships1/4594533224

 

[The_Doc_Man]

@Pat Hartman and @isladogs - don't disagree, I was giving a very short answer. And logically a junction table is a go-between that stands between two non-PK fields. It's just that you have to have a PK somewhere for the RI to kick in. By its very nature, of course, it is impossible for Access to manage RI directly between two fields - with one exception. If EITHER of the two data fields was a failed candidate key and the failure wasn't due to uniqueness issues, then a one-many relationship is still possible. Not preferred, but possible.

My answer was to notify our friend jaryszek that a linkage was possible but just not easy and not direct. I figure if he wants more, he will ask. If he doesn't, he won't. I've figured out that he's a direct-enough guy to ask for what he wants.

 

[jaryszek]

thank you Guys!!!

Yes i am always asking because when i am doing something i do this in good way Or trying to understand all and be sure what i am doing.

After discussion i fixed relationships:

Thank you Guys for sharing with your knowledge and magnificient experience.

Best,
Jacek