Should all database tables have a primary key?

Question

Is it good practice to give every database table a primary key? It seems to me that if the primary key is not explicitly needed, then it would just be extra clutter in my database.

Answer 1

Yes, it is good practise to have a primary key on every table.

But, NOT every table should have a single auto number id column. I felt the need to spell that out, because for some reason lots of people tend to throw in an extra ID in all tables even though a perfectly good candidate already exist. For example, a many-to-many table representing Users <-> Groups should use {user_id, group_id}.

Apart from stopping duplicates at the door, a primary key constraint also carries information which is used by the optimizer when generating execution plans.

This is why I always, or at least with very few exceptions, have a primary key on all tables I create. In fact, I even create primary keys on reporting tables where most of the columns are part of the primary key. Because during development, I will get at least one unique constraint violation because I did something wrong. With shitloads of data and no constraint in place I wouldn't have spotted the error.

Answer 2

The purpose of keys in relational database design is to prevent duplicate data and thereby help ensure data integrity. If you permit duplicate rows then you create ambiguity, potential update anomalies and very possibly incorrect results. Therefore in general every table ought to have at least one key (and maybe more than one if required). It's not usually the case that data integrity is "not explicitly needed"!

A primary key is just any one of the keys of a table. Designating one key as a primary key can be useful but is not particularly important - the important thing is that each table have at least one candidate key.

The reasons why duplicate rows ought to be avoided are covered pretty extensively in database literature. See:

http://www.dbdebunk.com/page/page/627052.htm

http://www.dbdebunk.com/page/page/638922.htm

http://dl.acm.org/citation.cfm?id=77708

http://www.amazon.com/Practical-Issues-Database-Management-Practitioner/dp/0201485559

Answer 3

I have a table that is partitioned by CreateDate which is not unique. In my case I have decided to remove the primary key from this table because the fact that a primary key index must be unique meant that I couldn't make this index a LOCAL index, instead it has to be GLOBAL. This would have meant that any deletion from that table (amongst other actions) would have made this primary key index unusable, rendering it useless.

Answer 4

It's not required, but be sure that you don't ever need one. The purpose of the primary key is so that you can uniquely identify one row from another based on a (usually minimal) set of criteria. This allows the database to ensure that you don't have duplicate data, for example, which also allows your database to conform to 1st normal form. If this isn't required then you don't need a primary key, but think carefully about it first.

Don't forget that the primary key doesn't necessarily have to be an additional column that contains an arbitrary unique value - it can also be a set of columns which together define uniqueness (e.g., a person's first name, last name and date of birth in an address book table).

Answer 5

While primary keys are hugely useful (and I use them all the time), let's be clear that there's no need to create a primary key if you don't need one. There are cases where you don't need one, among them:

• a table that just collects data (for persistence purposes) to be queried itself and not related to other tables where the other tables needed to find the precise row being related to
• you don't need to enforce any kind of uniqueness in your table

In both cases above, you may be interested purely in aggregate info about a table and not in identifying a row uniquely. I believe there are others. But not using primary keys are fine -- this is why they are not required when you create a table (in most systems).

Answer 6

When you probably WOULD:

In an OLTP database you'd almost always (in my case always) have a primary key of some sort. Sometimes Guid, sometimes autonumber/identity fields, sometimes set by the application or the customer. Sometimes even a combination of more than one field. This is because you'll typically want to uniquely identify any given row from the table.

Also, a primary key is a constraint used by the query optimiser that should improve performance for lookups and joins.

When you probably WOULDN'T:

The only time you wouldn't have a primary key is in a "reporting" table, probably in a denormalised data warehouse.

Answer 7

Generally yes - I'd make exceptions for tables that are simply rolled up versions of 'real' data stored for reporting purposes (i.e. rollup tables created for reporting/performance reasons), but generally I always have a primary key - and in my apps, its almost always an auto-increment integer that takes almost no extra space relative to the row size.