Timeline for When should a primary key be meaningful?
Current License: CC BY-SA 3.0
20 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 9, 2020 at 15:32 | comment | added | Solomon Rutzky | @Manngo Thanks! And maybe not so much in our "control", but at the very least we can have confidence that it's both reliable (there will always be a value and in the proper format/type and it will be unique) and stable (value won't change, at least not outside of some support scenario that isn't the normal operation of the system). So, I guess it is in our control in the sense that by using a surrogate we are preventing those anomalies. | |
| Mar 8, 2020 at 23:27 | comment | added | Manngo | 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. | |
| Mar 28, 2018 at 22:28 | comment | added | Solomon Rutzky | @WalterMitty Thanks. And fair enough re: "represent" vs. "support". I made that change. I also updated towards the bottom to try to further clarify the distinction between the idealism of the conceptual model and the pragmatism of the physical model. | |
| Mar 28, 2018 at 22:20 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added clarification |
| Mar 25, 2018 at 12:38 | comment | added | Walter Mitty | Excellent summary. One quibble. Where you said "support many to many relationships" I would have said "represent many to many relationshps". This idea that the physical layer represents data that is discovered at the conceptual layer turns out to be very helpful, imo. | |
| Mar 21, 2018 at 20:36 | comment | added | Solomon Rutzky | @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ʊ/ 😉 . | |
| Mar 21, 2018 at 20:12 | comment | added | Manngo | 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. | |
| Mar 21, 2018 at 20:04 | vote | accept | Manngo | ||
| Mar 19, 2018 at 16:26 | comment | added | Solomon Rutzky | @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. | |
| Mar 19, 2018 at 16:15 | comment | added | nvogel | 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. | |
| Mar 19, 2018 at 14:44 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added clarification |
| Mar 19, 2018 at 14:13 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added clarification about physical vs conceptual model |
| Mar 18, 2018 at 18:37 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added reason for "sibling" tables |
| Mar 18, 2018 at 15:14 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added pseudo-table |
| Mar 18, 2018 at 15:06 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added pseudo-table |
| Mar 18, 2018 at 14:56 | history | edited | Paul White♦ | CC BY-SA 3.0 | The @! It does nothing! |
| Mar 18, 2018 at 14:54 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added "acceptable" reasons for natural keys |
| Mar 18, 2018 at 14:29 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added reasons for preferring surrogate keys |
| Mar 18, 2018 at 14:22 | history | edited | Solomon Rutzky | CC BY-SA 3.0 | added pseudo-table |
| Mar 18, 2018 at 14:16 | history | answered | Solomon Rutzky | CC BY-SA 3.0 |