When creating a table in your database, is it necessary to have a primary key ?
Thanks in advance,
For further actions, you may consider blocking this person and/or reporting abuse
When creating a table in your database, is it necessary to have a primary key ?
Thanks in advance,
For further actions, you may consider blocking this person and/or reporting abuse
Dmitry Romanoff -
Michael Tharrington -
Luciano Jung -
dev.to staff -
Top comments (5)
There's no correct definitive yes and no answer to this question. It's circumstances dependent. Most of the times, you want a primary key because it helps the database to keep data integrity and it can have a performance impact - but that doesn't meant that every table in every database must have a primary key (or any key, for that matter). Codd purist would claim that every table in a relational database must have at least one natural key, since the relational model prohibits duplicate tuples in a relation (tuples translates to rows, relation translates to a table) - but personally, I find that too close-minded, almost a religious belief.
I am also motivated to believe in your point. As you can see in the above schema, the tables
movies_cast,movies_director,movies_genresdo not have a primary key.But then again a question pops up, how will it be indexed at this point?
Will the search become O(n) here or what happens?
An index doesn't have to be tied to a primary key, however I would advise to use a composite primary key on the tables you've mentioned.
Adding to this, the ordering of columns in the composite key is important: if
movies_directorshas a primary key(movie_id, director_id), searching by a movie_id will scan the primary key index, but searching by a director_id has to perform a sequential scan of the table unless there's a secondary index on that column.Is it actually possible to create a table without a primary key?
You can never have two database rows that are exactly the same, so basically, if you don't define a primary key, that's basically the same as having a composite primary key consisting of all fields. In fact, I wouldn't be surprised if many databases actually implement it like that under the hood.