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,

Comments

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

[Medam Mahesh]

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?

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

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

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