Can a database table be without a primary key?

Question

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.

Answer 1

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 2

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 :(.