I recently made a table that looks like the one below

  subindusty_id INT UNIQUE NOT NULL,
  -- other fields...

I realized that in this table I could just remove the column called id and make subindustry_id the primary key.

Thinking back, the only reason I can think of for why I didn't make subindustry_id the primary key is for the sake of consistency, considering that I have a lot of other tables with a column called id.

Except for things like consistency*, are there any tangible benefits/downsides to having this additional id column as the primary key when there's already another column that'd be a good primary key?

*Consistency as in "that's the way the rest of the tables look". The kind where you're enforcing a certain type of style. I guess the point of this question is to question the practice of giving every single table an id column regardless of the fact that they might already have a more suitable candidate for a primary key.

  • 2
    There are a lot of questions here about it: dba.stackexchange.com/search?q=surrogate+key and in SO: stackoverflow.com/search?q=surrogate+key – McNets Jan 19 '17 at 18:33
  • I appreciate the links, but from what I've seen they don't answer my question. Either it's some debate about 3NF or they assume that the natural key will change. Regarding 3NF, I don't care about that for this question, and regarding the uniqueness of the natural key, I have a UNIQUE constraint on it because I'm sure that it'll never change. – m0meni Jan 19 '17 at 18:47
  • 3
    @AR7 it seems as if you know a lot about this subject and you're asking us to repeat it because you're unsatisfied with what you know. Well we to know what you're looking for then. You don't just get to ask a vague question that requires brain dumps from everyone. Well, we'd rather you not anyway. That said it's a very frequent thing not to have a surrogate key. I do it all the time. Literally.. Every. Project. That said, lots of my tables have surrogate keys too. – Evan Carroll Jan 19 '17 at 19:21
  • 1
    @EvanCarroll I've never thought about it as "it seems as if you know a lot about this subject and you're asking us to repeat it because you're unsatisfied with what you know" before, and you're 100% right. I know the theory, but I have no experience so I'm trying to crutch on the dba community for that. I guess I have to figure it out for myself. Thanks :) – m0meni Jan 19 '17 at 19:27
  • 1
    For this particular example the id column is useless and can be removed without any problems. It adds no value and just increases the space used by the table and adds the burden of a second index that needs to be maintained. – a_horse_with_no_name Jan 19 '17 at 21:30

I agree a lot with comments from @MDCCL:

You should analyze each particular case —that is, every particular table within its whole context, that is, the entire database structure— to define if it requires the appending of a system-generated surrogate column, as such an artifact is an additional aspect that requires its particular administration. Consistency with the way the rest of the tables look is irrelevant, from the logical and physical points of view

A few pointers from my side (the ones that come to my mind at the moment, and that probably are not all there is to it)

Negative consequences:

  1. You need more space for everything: each row is wider, and you have an extra index to maintain. Every write will take (a little bit) more time, and so will every read, because you use more space to read and write the same real information. Let's say you have a log of the readings of several thermometers, one per second. You have one table thermometers (thermometer_id PK, ...etc...) and one readings (thermometer_id, reading_timestamp, value_read). Your readings table has a natural key: (thermometer_id, reading_timestamp), because you cannot have two different readings at the same time. I would normally make this my PK, and forget about surrogates.

  2. Using surrogate keys tend to obscure the real structure and meaning of your data. For instance, imagine you have a tables persons, books and books_liked_by_persons. This last table is a m x n table, and most of the times contains only (person_id, book_id) pairs. Adding a third id is in such cases most probably meaningless and noisy.

Positive consequences:

  1. In some very specific cases, you may want to have different tables with share one part of their structure (let's say, for instance, you want some of them to have always a country_code, and xxx_id) so that some application is easier to develop because the same pattern can be used to process different tables. [This tends to be rather uncommon, AFAIK, but is also possible.]

  2. Using a surrogate key, even if there are some other alternative keys might help you save space (and probably, time). Imagine your natural key is a PGP key (note 1). It takes a char(800) (approx.) to store it. Imagine you have several tables that reference this one. It will save (a lot of) space all around to have a surrogate integer key (8 or 16 bytes) on the original table, because the referencing tables will store 8 (or 16) bytes instead of 800. The accounting needs to be done for each specific case.

  3. If you ever have to edit your data "by hand" (and, some day, you probably will), you don't want to look for keys which are really long. You prefer surrogates in that case.

  4. Some databases (not PostgreSQL, at least as of now) will cluster the data of your tables using your primary key as a clustering index (or can do it by default unless you explicitly state the contrary). In such cases, it is normally advisable to have an always incrementing primary key; to avoid fragmentation.

So: every case needs analyzing, weighting and deciding. Sometimes consistency may be confused by flying on autopilot.

Side note: There are several different naming conventions. I prefer the one where id is never used, and a persons table has a person_id column if needed.

Note 1: A PGP key looks more or less like:

mQENBFiBGbcBCADBAe1p70ZPGwM6gmAAFTd13gv68rOUQEqlhddjkJZoiKs8SJuX m4Hpu29+4msQ91071R2yGZ+iydO2laRqWD1jFmF1qOTWiwYkrxYc288/XLbGi7NY gz9CPKlaH8N4VJBPC6eoRJgpUawBygERf+YFxBEXipWrZbusFrnHCilGGuOwIDAc UmYf7XNFZrg4+i+QEkBqWnBtd4Q7tkdTTywFDMZ3H+fwrDp+8M3zUwi10PCxJbQd JkKzXDFemaOHiDxWKNRg7CFyCBRq5K6mfXZuPeAtVOxFMFRnvyJ75HWv4Ss5n40b tpL/kmSQ9cHgl0nWHS+LMF55zZsAKToNaW6FABEBAAG0FXNvbWV0aGluZ0Bub3do ZXJlLmNvbYkBHAQQAQIABgUCWIEZtwAKCRDigzJ94dxj1hujCACFt22wZD5yn5/J 3cGLonszqgDRMNzmkdlJjiZi+2fC51z2mkQcy3rlzrQX9K1Mu3TLxPfwyMMiZkut trleZI4KyAD1hQRm4hQ4f+xx++xOqnMh5c7KsE6AwnGxeDgLy8hD3IJCmrDFwZ93 4XgZ+iXEzFxMcwmLeJ7xg7re4NTlovpIj1QetXqfEHydHQ1AJCqBjGSs805DBHGO nKAwBXuPI67Kcry+HIhn4ZaIXmXLGvXStJAz2bX8o/nQaVoC/YOyUTMZadR3jtdf ka5OeyvudCiKTNL3I1oDbhFFfGEhF2w66wp8acJ4z5bYkaQ5RFpEMI1ISfSn7Ypg FZln1sTo =RYbd

| improve this answer | |
  • never use id it should be a cardinal sin. – Evan Carroll Jan 19 '17 at 21:07
  • 1
    For the example in the question the id column is useless. It adds no value and doesn't save space because it's an int just like the natural primary key. – a_horse_with_no_name Jan 19 '17 at 21:28

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