• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

composite primary keys and relationships

hey guys, i've got this table called CodeDefinition and i'm trying to link it to this other table called CodeStorage. CodeDefinition has a compsite primary key and here's what i see in the Indexes window.
CodeDefintion TableCodeDefinition's indexes Window
in the index window it clearly states the the index is unique. however when i try to join the relationship from CodeDefintion.Cat1 to CodeStorage.Cat1, this is the error message i get.

Relationship joining Error
so i go look at the field properties of the table and here's what i see
CodeDefinition's Field Properties
i think it's strange cause the indexes window says that the indexes are all Unique but the field properties says it's not even indexed.

1) why is there a difference between indexes window and field properties
2) how do i fix this?

if i go to the field properties and change the index field from no to yes (no duplicates) then an extra index is added to the table as per the below picture and i can form a relationship. is this the right way to solve it? i think the main problem is how come the indexes window shows a index for the primary key but the field properties doesn't show

fix
3) guys an additional question is - i guess i'm trying to join 2 tables using a composite primary key. it is made up of Cat1, Cat2 and Cat3. what is the right way to join tables using a composite primary key?
0
developingprogrammer
Asked:
developingprogrammer
  • 3
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<1) why is there a difference between indexes window and field properties>>

 It's different because there is not an index on that individual field.

<<2) how do i fix this? >>

  Join on all three fields or create an index on Cat1 and then join on that if that is what forms the relationship.

<<3) guys an additional question is - i guess i'm trying to join 2 tables using a composite primary key. it is made up of Cat1, Cat2 and Cat3. what is the right way to join tables using a composite primary key?>>

 You have to join on all three fields of the Key, not just one of them.

 And FYI; anytime you have repeating fields in a table, it's usually a sure sign that the table design is incorrect.

Jim.
0
 
developingprogrammerAuthor Commented:
Whoa cool!!!! I just discovered that my relationship can join on multiple fields like what you said Jim!! Haha show how super noob I am - but a determined noob!! = ))

Jim was there anything you saw on my tables that was incorrect? I'm in the process of reading the relationships article by jdeffman. Really helps a lot! = )
0
 
developingprogrammerAuthor Commented:
Jim just completed your article on PKs!! It's really really helpful!! Now I know how to design my tables properly = )
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Whoa cool!!!! I just discovered that my relationship can join on multiple fields like what you said Jim!! Haha show how super noob I am - but a determined noob!! = ))>>

 We all start at some point; I'm just getting C# and .Net under my belt.

<<Jim just completed your article on PKs!! It's really really helpful!! Now I know how to design my tables properly = ) >>

 The key article just covered keys.  If you want to get into normalization (proper table design), you might want to spend some time on this:

HOW TO: Introduction to Application & Database Design .
http://www.youtube.com/watch?v=4YH6gG0Y660

  It's about an hour, but I think you'd find it worth it.

  Of course there are many good articles, video's, and past threads here on EE covering normalization and table design.    Would be worth it to look around a bit before you dig in again.

Jim.
0
 
developingprogrammerAuthor Commented:
Hrmm yea I've actually been through hours of courses (online) into databases, normalisation etc optimisation but I guess it is always quite general and sometimes I miss out on some specific things. Yup I definitely want to check out that video. Thanks Jim for your suggestion and help!! = ))
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now