Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
Let's say that I have an articles table that have two columns: title and content, and let's say that this articles table doesn't have any relationship with any other table.
Why would this articles table have a primary key? I mean, what problems could I face if the articles table doesn't have a primary key? Having identical rows is not a problem because two or more articles can have the same title and content.
The main benefit of a primary key has nothing to do with foreign keys. A primary key allows you to identify a single record in that table. Presumably, the system will have multiple articles. If all your application ever does is show a list of articles, then a primary key won't be much use. As soon as you want to show just a single, specific article, the primary key becomes mandatory.
When showing a single record to the end user, do not assume an index within the result set is enough. Consider a case when a user chooses to view article number 2. While viewing the list of articles, someone adds another article. Depending on how you sort the result set, showing "article 2" might end up showing article number 3.
Primary keys are also necessary for discrete, accurate updates. The primary key would be a discriminator value used in the UPDATE statement in order to ensure you don't accidentally update the wrong record (or no record at all).
update articles
set ...
where id = 5;
Same thing for DELETEs.
You need primary keys on a table if you want to reliably:
Changing data without referencing the primary key value is risky in most use cases, and I do not recommend doing it.
rowid
Apr 7 at 14:37
In SQL, rows are identified solely by their values. If two rows have exactly the same values, there is no way to distinguish them in a query. The standard SQL operators SELECT, UPDATE, DELETE always operates on sets matching a criteria, so if you have two duplicate rows there is no way to update one without updating the other.
It is easy to see how this can lead to all sorts of problems. Let's say you have a GUI which allow you to edit articles individually. During the editing of one article you bring it into a state where title and content it is exactly the same as another article. From this moment on, any update will update both articles. They now are "locked together". You can't even delete one of the duplicates, since a DELETE that match one will also match the other.
It is possible to circumvent this issue with advanced SQL. For example, SQL cursors can be used to iterate through a table and update rows individually even if there are duplicates. But this is a complex and slow solution to a problem which is easily avoided in the first place by ensuring all rows are unique.
SQL has a somewhat confusing relationship to duplicates. The relational model which underlies SQL assume rows are always unique (ie. every table has a primary key or unique constraint) but SQL does not enforce this when creating tables. So you end up with duplicates being allowed but giving all sorts of problems. So just avoid them.
rowid which is the physical location of a row
Apr 8 at 19:06
You write
having identical rows is not a problem because two or more articles can have the same title and content.
But it is in fact a problem and it is exactly why I always have a surrogate key in every table:
If you can have two articles with the same title (and contents) and you don't have any unique key, then you have no way of deleting one of them without deleting the other as well.
To add to this, when your API is consumed by e.g. a mobile application, the application need to be able to uniquely identify articles e.g when an article headline is pressed to open the article's body, that will then send a GET request full article's body by ID. When designing systems, you need think outside the box and realise that ultimately, what you are designing is not for you but for whoever consumes your API.
Additionally, IDs help ui with scrolling performance (but that is a bit off-topic here)
If you can have “two or more articles can have the same title and content” then why allow the second or third? What does it mean to have a dozen or a million identical articles?
Either it means something, and you need a way to differentiate them, or it doesn’t and it’s a waste of resources which you should prevent. If you differentiate them, then you have a primary key (which may be a composite key).
A primary key is nothing more or less than a way to uniquely identify a row of data.
having identical rows is not a problem because two or more articles can have the same title and contentironically, this IS actually a problem if you don't set a primary key colum. In a table without a primary key, when two or more rows have identical values in all of their fields, the DBMS has no way to diferentiate one from the other, so in the end they will be treated as the same row and, for example, trying to update one will either update all of them, or cause an error as the DBMS is unable to identify which one you're actually trying to update