Codd, Mote, Mom, Dad and all other right-thinking individuals say you should define a primary key for each table. Most database products, however, actually allow you to create tables without primary keys. They may warn you about the danger of keyless tables, but they let you ignore the warning. If you choose to do so, then you're running a bit of a risk.
The following table does not have a primary key. Upon initial inspection, it looks perfectly fine. It looks like eight college age people.
| Name | Age | Gender | Phone |
|---|---|---|---|
| Robert | 22 | M | 555-6435 |
| Edward | 20 | M | 555-8448 |
| Allison | 22 | F | None |
| Melinda | 19 | F | 555-9042 |
| Robert | 17 | M | None |
| Terry | 24 | F | 555-0732 |
| Martin | 22 | M | 555-8448 |
| Terry | 19 | M | None |
The problem with the keyless table should be obvious. How do you uniquely identify the people? The Name field isn't sufficient since there are two Roberts and two Terrys. Likewise, the Age field (two 19s and three 22s), Gender field (three Fs and five Ms) and Phone field (two 555-8448s) cannot uniquely identify the people.
You might try to get around this identification problem by considering multiple fields. For instance, it appears that Name and Age taken together uniquely identify the people in the table.
| Name | Age | Gender | Phone |
|---|---|---|---|
| Robert | 22 | M | 555-6435 |
| Edward | 20 | M | 555-8448 |
| Allison | 22 | F | None |
| Melinda | 19 | F | 555-9042 |
| Robert | 17 | M | None |
| Terry | 24 | F | 555-0732 |
| Martin | 22 | M | 555-8448 |
| Terry | 19 | M | None |
One Robert is 22 and the other is only 17. One Terry is 24 and the other is 19. So, Name+Age works... for now. But what happens if another 19 year old male named Terry is added to the table? And what if he doesn't have a phone?
| Name | Age | Gender | Phone |
|---|---|---|---|
| Robert | 22 | M | 555-6435 |
| Edward | 20 | M | 555-8448 |
| Allison | 22 | F | None |
| Melinda | 19 | F | 555-9042 |
| Robert | 17 | M | None |
| Terry | 24 | F | 555-0732 |
| Martin | 22 | M | 555-8448 |
| Terry | 19 | M | None |
| Terry | 19 | M | None |
Now you're stuck. Without a primary key, there is no way to differentiate the two guys.