Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

8
  • 1
    In your modified answer you are now talking about foreign keys rather than primary keys. Not all primary keys are referenced by a foreign key; not all foreign keys reference a primary key. Furthermore, the issues you talk about apply also to surrogates: company mergers or re-orgs can mean that a single-attribute surrogate is no longer sufficient and/or needs to be updated. Surrogates do get updated during schema redesigns or data fixes. Surrogates can sometimes be more stable than natural keys but not always. What matters is that you evaluate these considerations on a case-by-case basis. Commented Mar 19, 2018 at 16:15
  • @sqlvogel I am presenting a pragmatic approach based on probability, not possibility. Yes, PKs don't always have FKs, and FKs can reference unique constraints, and tables with surrogate keys do get merged, updated. The issue is reducing the chances of having large schema redesign projects, etc. Of course they cannot be eliminated, which is why it helps to reduce the scope of what those inevitable changes will be. Commented Mar 19, 2018 at 16:26
  • Thanks for your answer. I also agree with your two exceptions, except to note that they are only natural because they are based on foreign keys to surrogate primary keys. Commented Mar 21, 2018 at 20:12
  • @Manngo Yer welcome, and thanks :). And yes, I did emphasize that even in these two exceptions, they are not truly natural keys, they just aren't new surrogate; they are pre-existing surrogates. But since they are already existing, as far as the new table is concerned, that is a form of "natural" since it is not a newly created, arbitrary value. But you know, /təˈmeɪtoʊ/, /təˈmɑːtoʊ/ 😉 . Commented Mar 21, 2018 at 20:36
  • 1
    I have re-read your excellent answer yet again, and I really like your observation “we don't control the external world”. In this regard, a surrogate primary key is part of the internal world, and about the only thing we can control. Commented Mar 8, 2020 at 23:27