Primary Key – With / Without

Answer One:

Should each and every table have a primary key?

Short answer: yes.

Long answer:

  • You need your table to be joinable on something
  • If you want your table to be clustered, you need some kind of a primary key.
  • If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records?

In MySQL, the InnoDB storage engine always creates a PRIMARY KEY if you didn’t specify it explicitly, thus making an extra column you don’t have access to.

Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in anUNIQUE index.

Answer Two :

Always best to have a primary key. This way it meets first normal form and allows you to continue along the database normalization path.

if you don’t have a primary key then you are going to have duplication of records somewhere in your table.

if you don’t have an index, BUT a primary key gets an index by default

Use Cases : a table not to have a primary key?

I tend to agree that most tables should have a primary key. I can only think of two times where it doesn’t make sense to do it.

  1. If you have a table that relates keys to other keys. For example, to relate a user_id to an answer_id, that table wouldn’t need a primary key.
  2. A logging table, whose only real purpose is to create an audit trail.
Advertisements

Tagged: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: