 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / January 2008creating relationship involving multiple-field keys
Hello everyone, I'm in the process of designing my first database. I've been doing a good bit of research and studying, but I haven't been able to find a solution to my current dilemma. The short version is that I have one table with a two-field primary key that should have a 1-to-many relationship to another table with a three-field primary key. The two fields for the first key are both in the second key. I can create the relationship, but cannot enforce referential integrity. When I try to do so, I get an error saying, "No unique index found for the referenced field of the primary table." I don't understand why this is happening or how to fix it. The longer version, with table definitions: The database deals with grant accounting. I work in a department that does medical research that is funded by federal grants. Some grants (but not all) are split into "cores", which are basically projects. We hire on a number of contractors to assist with our research, but some contractors work on more than one grant, and some work on more than one core in the same grant. The four tables most relevant to this issue are below: t_Consultants *EntID (PK) *ConsFirstName *ConsLastName *ConsDateCreated *ConsDateUpdated t_ConsAlloc *EntID (PK) *GrantID (PK) *CoreName (PK) *ConsStartDate *ConsEndDate *ConsAllocDateCreated *ConsAllocDateUpdated t_Cores *GrantID (PK) *CoreName (PK) t_Grants *GrantID (PK) *GrantName *GrantYear *GrantStartDate *GrantEndDate *IDC What I want to do is create a 1-to-many relationship with referential integrity enforced between the GrantID and CoreName fields, from the t_Cores table to the t_ConsAlloc table. When I try to do this, I get the error mentioned above (no unique index found...). If I had to guess, the fact that CoreName can be null in t_ConsAlloc is the big issue here. I can't find a good way around it (aside from defining a nonexistent core for each grant that doesn't have cores) because EntID and GrantID aren't enough to define a unique entry, yet many grants don't have cores. Also, the related fact that there will be grants in t_ConsAlloc that are not in t_Cores may also be an issue. Thank you to anyone who read this far. I appreciate any help in advance. --DanC There may be other ways to do it but I would have your t_Cores table have its own primary key, eg ID (Key) GrantID CoreName Then I would use this ID in your relationship with t_ConsAlloc. -Dorian > Hello everyone, > [quoted text clipped - 64 lines] > > --DanC If I'm understanding correctly, I don't believe that would work when the consultant was working on a grant without cores. The only way to account for both grant types then would be to have two different foreign keys in t_ConsAlloc (one to GrantID in t_Grants, one to the hypothetical CoreID in t_Cores), where only one was relevant to each record. Is my logic correct here, or am I misunderstanding something basic? I've noticed that, while learning, about a third of my problems are real issues while two-thirds are just due to being new to database design. I think I'm dealing with a real issue this time, but could be mistaken. Thank you for your help. --DanC > There may be other ways to do it but I would have your t_Cores table have its > own primary key, eg [quoted text clipped - 76 lines] > > - Show quoted text - I'm not familiar with your application but I assumed the purpose of the t_cores table was to list all the unique combinations of Grant and Core. If there are no cores, there will still be a record in this table but with the CoreName blank. There will be a one to many between t_Grants.GrantID and t_Cores.GrantID t-Cores.ID will be related to t_ConsAlloc.ID t_ConsAlloc.EntID will be related to t_Consultants.EntID -Dorian > If I'm understanding correctly, I don't believe that would work when > the consultant was working on a grant without cores. The only way to [quoted text clipped - 93 lines] > > > > - Show quoted text - Okay, I see what you mean now. I had only added grants with cores to the t_Cores table, but your solution does look like the most straightforward approach. Thank you for the help! --DanC > I'm not familiar with your application but I assumed the purpose of the > t_cores table was to list all the unique combinations of Grant and Core. If [quoted text clipped - 105 lines] > > - Show quoted text - You just have to make sure you add the t_Cores blank record whenever you create a new Grant. Then you have to cater for that when you add the first Core (replace the blank record instead of adding a new one). Like I said, there may be other ways to do it, but that's the only one that comes to me right now. -Dorian > Okay, I see what you mean now. I had only added grants with cores to > the t_Cores table, but your solution does look like the most [quoted text clipped - 113 lines] > > > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.