By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

table composite primary key and one to many relationship

P: 4

I have a junction table with a composite primary key (ie. two field are used to define the primary key). I need link a third table to the junction table in a one to many relationship. In the attached photo relation_probleme.jpg, the table without any relations should be on the "many" side and the junction table on the "one" side.

The idea is that there are a many "elements" elements linked to many "inspections" and vica-versa. for each pair element-inspection i want to attribute a "condition", in turn this one "conditon" can be linked to many "recommandations".

I have considered using a single field primary key in the junction table called id_cond, but I don't want to do it like this because i don't want to the possibility of an element being attributed multiple conditions for the same inspection.

So how do I do it?
May 10 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,492
You can link [tbl_cond_recommand] by adding both fields of the composite PK of [tbl_condition_ele] to it.

However, you can also assign a single-field, unique PK to [tbl_condition_ele] if you choose (and there are reasons why it would be advisable). Having a PK doesn't stop any other indices from being set up as mandatorily unique.
May 10 '12 #2

P: 4
Hello. Thank you for the info about being able to have unique indices that aren't primary keys. I used it in a slightly different way than you suggested. I made a triple field primary key and made one of those fields unique (Noname.jpg). Does anyone have any feed-back about which of the solutions presented is best, and why?

May 11 '12 #3

Expert Mod 15k+
P: 31,492
That's not a good idea Emily. Never make an index more than it needs to be. [id_cond] can (should) be the PK and a compound index including [id_element] & [id_inspec] can be set up as unique. What more you may need/require is down to what you require the design to do for you.
May 11 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.