DEV Community

Medam Mahesh
Medam Mahesh

Posted on

Is Primary Key necessary in a table (DBMS)

When creating a table in your database, is it necessary to have a primary key ?

Thanks in advance,

Top comments (5)

Collapse
 
peledzohar profile image
Zohar Peled

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.

Collapse
 
memahesh profile image
Medam Mahesh

Example Schema
I am also motivated to believe in your point. As you can see in the above schema, the tables movies_cast, movies_director, movies_genres do 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?

Collapse
 
peledzohar profile image
Zohar Peled

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.

Thread Thread
 
dmfay profile image
Dian Fay

Adding to this, the ordering of columns in the composite key is important: if movies_directors has 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.

Collapse
 
jasperhorn profile image
JasperHorn

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.