Do I need a primary key and should it be called id?

While working with databases – especially Mysql – most people seem to ask the same question over and over again when it comes to Primary Keys. Here’s my typical answer to the question(s) above (and more).

Do I need a primary key?

No, you don’t need it, but if you haven’t got one, there’s probably something wrong with your data model. A primary key is the unique key to a row in a table. It can be a single column, but in some cases it can be a combination of two or more columns.

Should the primary key always be a number?

No. Look at you data model and see what makes most sense. If you have an obvious candidate such as a date or a name, which is unique (for each row), use that.

For performance reasons, you should remember, that Mysql (and most other databases) works most efficient with numbers (especially used as indexes), and in some cases, it may be a good idea to invent a “id” column (as a unique number field) to use as a primary (and foreign) key in your data model. These cases include places where you have an inefficient primary key – a varchar field, a combination of several fields and likewise cases.

Should the primary key be called “id”?

Sure, if it fits your data model. The primary key column in a table doesn’t need to be called anything specific, but to help your self – if the primary key is a single column – you could follow this convention:

• If a single column is the primary key, call it id (even if it isn’t a numeric column).
• When referring to the primary key in other tables as a foreign key, call it the table name plus underscore plus id.

That makes it easier to read and understand the model.