16

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.

5
  • 1
    Think of the db client/application is a user/consumer of those primary keys; in some sense, to the external client these are foreign keys as the client's usage of them is ~similar to how another table might reference them (namely, as foreign keys). If it weren't for the application interacting with the database we might not need primary keys (or even tables).
    – Erik Eidt
    Apr 7 at 13:07
  • 11
    "two or more articles can have the same title and content" - can they, wouldn't they be the same article then? What would distinguish them? And why would you care how many articles with the same values exist - does it even matter if there's one, two, or more?
    – Bergi
    Apr 7 at 13:22
  • 28
    having identical rows is not a problem because two or more articles can have the same title and content ironically, 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
    – Josh Part
    Apr 7 at 15:15
  • 5
    @JoshPart Or in DBMS terms, that violates the first normal form (1NF).
    – iBug
    Apr 8 at 11:16
  • 2
    Why wouldn't it have a primary key? Why wouldn't you want to be able to distinguish unique articles that might coincidentally have identical content? Apr 8 at 14:19

5 Answers 5

68

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:

  • View a single record
  • Update a single record
  • Delete a single record

Changing data without referencing the primary key value is risky in most use cases, and I do not recommend doing it.

14
  • 35
    In short, the number of legitimate use cases for not putting in a primary key is small enough that you should just put a primary key into every table. Apr 6 at 20:05
  • 7
    This is the right answer. As an additional note for the OP, unless you know what you are doing, I would recommend that all tables have a primary key that is immutable, and if that is not possible with a natural key then a surrogate key should be introduced.
    – John Wu
    Apr 7 at 1:04
  • 2
    @RobertHarvey I once had to delete rows from a table of "bonus coupons" that only had a customer id and a single customer having three coupons was represented by them having three identical rows. Still don't know if the DB admins were just joking around or not.
    – JollyJoker
    Apr 7 at 11:25
  • 1
    Don't many database engines add implicit, invisible primary keys if you don't? e.g. sqlite rowid
    – user253751
    Apr 7 at 14:37
  • 3
    @user253751: you need to be very careful about using those. They are meant to be internal identifiers for the database, not external identifiers for your queries. Many of these internal identifiers are partially generated by the physical location of data on disk. If that physical location can change, the rowid might change. The database and disk controllers manage the physical locations of data. Do not write application logic relying on this transient information. Apr 7 at 14:41
22

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.

10
  • There are db-specific tricks that make it possible to refer to n of m identical rows like Oracle's ROWNUM. Not a good idea to design a db that way, of course.
    – JollyJoker
    Apr 7 at 11:32
  • @JollyJoker But ROWNUM changes over time, as rows are inserted and deleted. So you can't easily use it to refer to a specific row unless you lock the table for the duration of the editing session.
    – Barmar
    Apr 7 at 14:16
  • @Barmar "For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows" It can't change over time, it only lives for a single query.
    – JollyJoker
    Apr 7 at 14:58
  • 3
    @JollyJoker Suppose you fetch 3 identical rows, edit 1 of them. Meanwhile someone else does the same thing. You each save your changes. There's no way to know whether your changes will affect the same or different rows, because ROWNUM is not a unique key across the two result sets.
    – Barmar
    Apr 7 at 15:37
  • 1
    @Barmar: I guess JollyJoker meant rowid which is the physical location of a row Apr 8 at 19:06
3

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.

0

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)

0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.