Solved Joining not primary key with enforcing referential integrity (1 Viewer)

jaryszek

Registered User.
Local time
Today, 14:21
Joined
Aug 25, 2016
Messages
756
Hi Guys,

I have relationships something like here:
1593526346672.png


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:
1593526493762.png


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:

1593526645579.png


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

Please help,
Best wishes,
Jacek
 

Attachments

  • RelationIssue.accdb
    480 KB · Views: 68

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,358
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:21
Joined
Feb 19, 2002
Messages
42,976
i am not using relationships here because i have already used them in separate case as above
That isn't the way relations in relational database work.

if tblServerDetails is related to tblServer, then make the correct relationship. It is not at all clear where tblVolumes fits into the picture. Perhaps its PK should be VolumeID and it should be related to tblServer. Then you can relate tblServerDetails to tblVolume on VolumeID if it is a child of tblvolumes which makes it a grandchild of tblServer.

In any event, data field to data field relationships cannot be resolved by any RDBMS, not just Access.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:21
Joined
Feb 28, 2001
Messages
27,001
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:21
Joined
Feb 19, 2002
Messages
42,976
Technically Doc, junction tables are datafield to PK. They are not datafield to data field. The two FKs are data fields but they each join to the PK of another table.

Also a join isn't the same as a relationship. You can join any two columns of like data type whether it makes any sense at all or not. The database engine is not judgmental. You can join the Employee table to the Job table on EmployeeID = JobID. You'll probably get matches especially if the two IDs are autonumbers since autonumbers typically start from 1. Will the results have meaning? No.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:21
Joined
Feb 28, 2001
Messages
27,001
@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

Registered User.
Local time
Today, 14:21
Joined
Aug 25, 2016
Messages
756
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:
1593578023302.png


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

Best,
Jacek
 

Attachments

  • RelationIssue.accdb
    512 KB · Views: 75

Users who are viewing this thread

Top Bottom