Page 1 of 1

the primary key

Posted: Tue Sep 08, 2009 8:18 am
by runeveryday315
id name favourite_foods
1 Sam Curry,Steak,Cheese
2 Lucy Chicken,Burgers,Apples
the table fail to 1NF ,so i separate out into two tables,
persons
id name
1 Sam
2 Lucy

person_favorite_foods
id favourite_food
1 Curry
1 Steak
1 Cheese
2 Chicken
2 Burgers
2 Apples

the Primary Key of table "person_favorite_foods" is id,but in "persons" table. id also the PK.is it ok?

Re: the primary key

Posted: Tue Sep 08, 2009 8:49 am
by onion2k
You're misunderstanding the concept of primary keys a bit. What you have there is good, but 'id' in person_favorite_foods is not the primary key, it's a foreign key. For what it's worth, I wouldn't call the key in person_favorite_foods 'id'. I'd call it 'person_id'. That's more meaningful - it's obvious what it is. 'id' could be anything.

Re: the primary key

Posted: Tue Sep 08, 2009 8:45 pm
by runeveryday315
onion2k wrote:You're misunderstanding the concept of primary keys a bit. What you have there is good, but 'id' in person_favorite_foods is not the primary key, it's a foreign key. For what it's worth, I wouldn't call the key in person_favorite_foods 'id'. I'd call it 'person_id'. That's more meaningful - it's obvious what it is. 'id' could be anything.
according to you said, there is no primary key in table "person_favorite_foods ",this is fail to 1 normal form.the table fit for 1 NF must have a primary key. am i wrong?

Re: the primary key

Posted: Tue Sep 08, 2009 9:00 pm
by califdon
The primary key in your second table could be a compound key, the concatenation of the 2 fields (which would then be unique, which is the requirement for a primary key). Or you could assign another field as a primary key, for example, an auto-increment field. There is one and only one prime requirement for a primary key, and that is that it is unique in the table in which it is the primary key. That's its whole purpose: to be able to identify every row with no ambiguity.