Can a database table be without a primary key?

Can anyone tell me if a table in a relational database (such as MySQL / SQL SERVER) 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.

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.

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.

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):

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.

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.

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.

I run into the same question on one of the tables i did.

The problem was that the PK was supposed to be composed out of all the rows of the table all is well but this means that the table size will grow very fast with each row inserted.

I choose to not have a PK, but only have an index on the row i do the lookup on.

When you replicate a database on mysql, A table without a primary key may cause delay in the replication.

[To see links please register here]

> The most common mistake when using ROW or MIXED is the failure to
> verify that every table you want to replicate has a PRIMARY KEY on
> it. This is a mistake because when a ROW event (such as the one
> documented above) is sent to the slave and neither the master's copy
> nor the slave's copy of the table has a PRIMARY KEY on the table,
> there is no way to easily identify which unique row you want
> replication to change.

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).

However, in your case (and 99% other cases too), I would **add a new auto increment unique column** like `temp_id` and make it surrogate primary key.

It **makes much easier** maintaining this table -- for example finding and removing records (i.e. duplicated records) -- and believe me -- for every table comes time to fix things :(.