the primary key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
runeveryday315
Forum Newbie
Posts: 3
Joined: Tue Sep 08, 2009 8:08 am

the primary key

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: the primary key

Post 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.
runeveryday315
Forum Newbie
Posts: 3
Joined: Tue Sep 08, 2009 8:08 am

Re: the primary key

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: the primary key

Post 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.
Post Reply