6

I read somewhere saying that every table should have a primary key to fulfill 1NF.

I have a tbl_friendship table.

There are 2 fields in the table : Owner and Friend.

Fields of Owner and Friends are foreign keys of auto increment id field in tbl_user.

Should this tbl_friendship has a primary key? Should I create an auto increment id field in tbl_friendship and make it as primary key?

zac1987
  • 2,721
  • 9
  • 45
  • 61
  • 1
    I think you have confused "primary key" with "artificial identifier" or possibly "surrogate key". Google for Chris Date's writings on [What First Normal Form Really Means](http://books.google.co.uk/books?id=y_eVBB5qdwMC&pg=PA107&dq=%22what+first+normal+form+really+means%22&hl=en&ei=diJJTqSqKtGq8QPRi9myBg&sa=X&oi=book_result&ct=result&resnum=1&ved=0CC8Q6AEwAA#v=onepage&q=%22what%20first%20normal%20form%20really%20means%22&f=false) – onedaywhen Aug 15 '11 at 13:47
  • 1
    **YES!** - *If it doesn't have a primary key, it's not a table* (Joe Celko) – marc_s Aug 15 '11 at 13:57
  • Should @table variables have a primary key? – Rod Sep 30 '22 at 13:18

7 Answers7

6

Primary keys can apply to multiple columns! In your example, the primary key should be on both columns, For example (Owner, Friend). Especially when Owner and Friend are foreign keys to a users table rather than actual names say (personally, my identity columns use the "Id" naming convention and so I would have (OwnerId, FriendId)

Personally I believe every table should have a primary key, but you'll find others who disagree.

Here's an article I wrote on the topic of normal forms. http://michaeljswart.com/2011/01/ridiculously-unnormalized-database-schemas-part-zero/

Michael J Swart
  • 3,060
  • 3
  • 29
  • 46
  • i though primary key must has unique value? the fields of Owner and Friend will have many duplicated values. Normally I will set the fields of Owner and Friend as Index (not Primary Key). Am I right or wrong? – zac1987 Aug 15 '11 at 13:04
  • 1
    Yes, uniqueness is a property of primary keys. It's unusual to have completely identical rows in a table, but if it's a requirement, then you can pick either no primary keys, or add the auto-increment column like you suggested. – Michael J Swart Aug 15 '11 at 13:09
  • -1, never ever use non-technical columns for primary keys, that will be the road to hell! Instead, use a technical primary key like "ID", like the OP suggested. – Doc Brown Aug 15 '11 at 13:10
  • @Doc Brown, what is "non-technical columns" ? – zac1987 Aug 15 '11 at 13:18
  • Hey Doc, never say never ever. But you're right in this case. I don't put pks on real values either. However, for relationship tables I like to use composite primary keys made up of foreign keys like (OwnerId, FriendId). – Michael J Swart Aug 15 '11 at 13:22
  • @zac1987: non-technical columns are those which represent data of the domain you are modeling (like "Owner" and "Friend"). Technical columns are helper columns which contain no such data (like an integer "ID"), which will be used only internally for relationships. Those columns should normally never be shown directly to a user of an application which accesses your tbl_friendship. – Doc Brown Aug 15 '11 at 13:24
  • I see that the OP has clarified that Owner and Friend are in fact foreign keys to tbl_User and hence, non-technical. – Michael J Swart Aug 15 '11 at 13:24
  • @Michael J Swart: you are right, I missed that. Please modify your answer a little bit, then I will remove my downvote. – Doc Brown Aug 15 '11 at 13:29
4

Yes every table should have a primary key.

Yes you should create surrogate key.. aka an auto increment pk field.

You should also make "Friend" an FK to that auto increment field.

If you think that you are going to "rekey" in the future you might want to look into using natural keys, which are fields that naturally identify your data. The key to this is while coding always use the natural identifiers, and then you create unique indexes on those natural keys. In the future if you have to re-key you can, because your ux guarantees your data is consistent.

I would only do this if you absolutely have to, because it increases complexity, in your code and data model.

Nix
  • 57,072
  • 29
  • 149
  • 198
  • May I know the Foreign Key of "Friend" must refer to auto increment id field from tbl_user or tbl_friendship? If tbl_friendship, why? – zac1987 Aug 15 '11 at 13:16
3

It is not clear from your description, but are owner and friend foreign keys and there can be only one relationship between any given pair? This makes two foreign key column a perfect candidate for a natural primary key.

Another option is to use surrogate key (extra auto-incremented column as you suggested). Take a look here for an in-depth discussion.

Tomasz Nurkiewicz
  • 334,321
  • 69
  • 703
  • 674
  • -1, never ever use non-technical columns for primary keys, that will be the road to hell! Instead, use a technical primary key like "ID", like the OP suggested. – Doc Brown Aug 15 '11 at 13:11
  • @Tamasz Nurkiewicz, I have update my question to make it clear. Thanks. – zac1987 Aug 15 '11 at 13:13
  • May I know how to make two foreign keys as a primary key? I mean how to set them become a primary key? – zac1987 Aug 15 '11 at 13:14
  • @zac1987: believe me, you don't want to follow this suggestion. For example, think about the problems you get if you try to change the content of Owner or Friend for an existing record. – Doc Brown Aug 15 '11 at 13:18
  • @Doc Brown, I can't think of any reason to change the auto-increment id from tbl_user.... if user delete a friend, just delete a set of primary key (both foreign keys) from tbl_friendship. The "change" u mean is deleting? I think won't have problem with it. – zac1987 Aug 15 '11 at 13:22
  • @zac: you are right, I thought you meant "Owner" and "Friend" were containing names, not IDs. If Tomasz will edit his answer a little bit, I will remove the downvote. – Doc Brown Aug 15 '11 at 13:31
  • *@Doc Brown*: I understand using natural keys is problematic if there is a slight chance of changing the key (like first name + last name - very poor natural key, and not even unique). But in this case you are referencing two other surrogate keys. What is the point of another column? The same thing applies to situations when you are absolutely sure *non-technical* column won't change (e.g. *maybe* SSN). – Tomasz Nurkiewicz Aug 15 '11 at 13:38
  • @Tomasz Nurkiewicz: I already wrote that I misunderstood the OP, because I did not read his later-added clarification immediately. But SO does not let me remove my downvote unless you edit your answer a little bit (for example, add some whitespace). – Doc Brown Aug 15 '11 at 14:23
  • *@Doc Brown*: I actually added some valuable link to my answer instead of whitespace ;-). Thanks in advance. – Tomasz Nurkiewicz Aug 15 '11 at 14:29
2

A primary key can be something abstract as well. In this case, each tuple (owner, friend), e.g. ("Dave","Matt") can form a unique entry and therefore be your primary key. In that case, it would be useful not to use names, but keys referencing another table. If you guarantee, that these tuples can't have duplicates, you have a valid primary key.

For processing reasons it might be useful to introduce a special primary key, like an autoincrement field (e.g. in MySQL) or using a sequence with Oracle.

Jules
  • 1,352
  • 7
  • 19
1

To comply with 1NF (which is not completely aggreed upon what defines 1NF), yes you should have a primary key identified on each table. This is necessary to provide for uniqueness of each record.

http://en.wikipedia.org/wiki/First_normal_form

In general, you can create a primary key in many ways, one of which is to have an auto-increment column, another is to have a column with GUIDs, another is to have two or more columns that will identify a row uniquely when taken together.

Bueller
  • 2,336
  • 17
  • 11
0

Your table will be much easier to manage in the long term if it has a primary key. At the very least, you need to uniquely identify each record in the table. The field that is used to uniquely identify each record might as well be the primary key.

Evernoob
  • 5,551
  • 8
  • 37
  • 49
0

Yes every table should have (at least one) key. Duplicating rows in any table is undesirable for lots of reasons so put the constraint on those two columns.

nvogel
  • 24,981
  • 1
  • 44
  • 82