Should every table have a primary key?

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 : Owne开发者_运维知识库r 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?

---

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/

---

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.

---

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.

---

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.

---

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.

---

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.

---

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.