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_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?
It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
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.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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.