Can a database table be without a primary key?

Question

Can anyone help me understand if a table in a relational database (such as MySQL) can be without a primary key?

For example, I could have table day_temperature, where I register temperature and time. I don't see the reason to have a primary key for such a table.

Thoughts?

Thanks, Boda Cydo.

Answer 1

If the possibility of having duplicate entries (for example for the same time) is not a problem, and you don't expect to have to query for specific records or range of records, you can do without any kind of key.

Answer 2

Wouldn't the primary key naturally be the time? would you ever have more than one temperature for a given time?

A better question to ask would be, "Why would you ever make a table without a primary key"

Answer 3

The time would then become your primary key. It will help index that column so that you can query data based on say a date range. The PK is what ultimately makes your row unique, so in your example, the datetime is the PK.

Answer 4

You don't need a PK, but it's recommended that you have one. It's the best way to identify unique rows. Sometimes you don't want an auto incremental int PK, but rather create the PK on something else. For example in your case, if there's only one unique row per time, you should create the PK on the time. It makes looks up based on time faster, plus it ensures that they're unique (you can be sure that the data integrity isn't violated):

Answer 5

Like always it depends.

Table does not have to have primary key. Much more important is to have correct indexes. On database engine depends how primary key affects indexes (i.e. creates unique index for primary key column/columns).

In your case I would add a new auto increment unique column like temp_id and make it primary key. It makes much easier maintaining this table -- for example finding and removing records (i.e. duplicated records).

Answer 6

I've got a better example of a table that doesn't need a primary key - a joiner table. Say I have a table with something called "capabilities", and another table with something called "groups", and I want a joiner table that tells me all the capabilities that all the groups might have, so it's basicallly

create table capability_group
(  capability_id varchar(32),
    group_id     varchar(32));

There is no reason to have a primary key on that, because you never address a single row - you either want all the capabilities for a given group, or all the groups for a given capabilty. It would be better to have a unique constraint on (capabilty_id,group_id), and separate indexes on both fields.

Answer 7

IF you ended up with duplicate entries you would struggle to update or delete one without a PK (though it would be possible via ROW_NUMBER())

Answer 8

Technically, you can declare such a table.

But in your case, the time should be made the PRIMARY KEY, since it's probably wrong to have different temperatures for the same time and probably useless to have same more than once.

Logically, each table should have a PRIMARY KEY so that you could distinguish two records.

If you don't have a candidate key in you data, just create a surrogate one (AUTO_INCREMENT, SERIAL or whatever your database offers).

The only excuse for not having a PRIMARY KEY is a log or similar table which is a subject to heavy DML and having an index on it will impact performance beyond the level of tolerance.

Answer 9

I would include a surrogate/auto-increment key, especially if there is any possibility of duplicate time/temperature readings. You would have no other way to uniquely identify a duplicate row.