Timeline for Is it good practice to always have an autoincrement integer primary key?
Current License: CC BY-SA 3.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 2, 2016 at 17:16 | audit | First posts | |||
| Sep 7, 2016 at 20:54 | |||||
| Aug 19, 2016 at 1:31 | history | edited | mcottle | CC BY-SA 3.0 | Added the stuff below "EDIT" to add a further example where autoincrement PKs aren't neccessary |
| Aug 19, 2016 at 1:26 | comment | added | mcottle | @Voo, that was my memory failing not data failing - look at my reply to Monty. VINs are an industry standard which has remained unique for decades. And when I wrote a system that used VINs I didn't use the VIN as the PK. I've seen people use licence plates as a PK - that didn't end well. | |
| Aug 18, 2016 at 19:55 | comment | added | Voo | I would say your post is actually one of the best examples of why picking things you think are unique as primary keys is pretty dangerous: Sure something may be unique now, but what about 10 years from now? 20? If that assumption ever becomes invalid (and one of the first samples you thought would be a great case for using a natural key, already turned out to be wrong!) you'll have lots and lots of refactoring to do. Also there's a performance hit from using anything but a single int primary key, although it's pretty negligible in most cases. | |
| Aug 18, 2016 at 19:19 | comment | added | djechlin | This is a bit of a nit, but in the interest of good writing, does the answer really need to start with "just to be contrary"? At best it's useless, at worst it invalidates the legitimacy of the whole answer. | |
| Aug 17, 2016 at 23:47 | comment | added | Zan Lynx | @Brad: Another way to do IDs for six columns and will create the same ID on separate databases is to use a hash of the row data as the ID entry. This works really well on fairly static data, less well if data fields keep changing all the time. It also saves on indexing since there's no need to maintain an index to enforce uniqueness. | |
| Aug 17, 2016 at 21:08 | comment | added | Erik Funkenbusch | It's truly amazing how few TRULY immutable "natural keys" there are. SSN's? Nope, they can change. It's rare, but it can happen. Usernames? Nope. Eventually someone will have a valid business reason to change. VIN is often a textbook example, but there aren't many others. Even home addresses can change, given street naming changes. | |
| Aug 17, 2016 at 8:19 | comment | added | mcottle | @SJuan If the company merges you have a remapping exercise whether you have two offices with the Key "NYC" or two offices with the key "1". If you create a second NYC office you create "NYC2" but it's more about retaining backwards compatibility with existing systems and reducing the training load on end users who are used to operating the gnarly old mainframe system from the '70s that uses "meaningful" TLAs as Keys... | |
| Aug 17, 2016 at 7:58 | comment | added | SJuan76 | You make a good point, but real world examples show that "natural keys" are fewer that we think... We got a network of offices and each office has an office code? And we are sure that management will not change that overnight? Sounds like a "natural key", until there is a merger with other company and some offices have to change their numbers to avoid collision. | |
| Aug 17, 2016 at 2:27 | comment | added | mcottle | @Brad, Nowhere in my answer was I advocating using a six element composite key. If you have one, it may be a surrogate Primary key (and should be enforced) but you don't whack 6 fields in every table that links to it. My message is about pragmatism and not blindly doing something because an ivory tower academic writes about something theoretical. | |
| Aug 17, 2016 at 2:22 | comment | added | mcottle | @Monty, my bad, you're right. Fallible memory, it's 20 years since I architected the fleet management systems. No the VIN wasn't the primary key :) I used an AutoInc Asset_ID IIRC which leads to something I forgot. Tables that are the linkers for many-to-many relationships where you link, say, car to accessory (e.g. sunroof) Many cars have many accessories so you need a "Car_Accessory" table which contains Car_ID and Accessory_ID but absolutely does NOT need Car_Accesory_ID as an AutoInc PK. | |
| Aug 16, 2016 at 20:27 | comment | added | AnoE | I admit some of these suggestions take it a bit far for me. Yes, being pragmatic is fine, but I cannot count how often someone swore the life of his firstborn that some attribute out of the domain will stay unique for the rest of days. Well, usually that worked well until the second week after going live, when the first duplicates turned up. ;) Using a "description" as a PK is just far out. | |
| Aug 16, 2016 at 17:51 | comment | added | Brad | Don't create a second, meaningless primary key; it's wasteful and may cause errors. However, if the way you establish uniqueness for a record is a combination of 6 columns then joining on all 6 all the time is very error prone unto itself. The data naturally do have a PK but you are better off using a id column and a unique constraint on those 6 columns. | |
| Aug 16, 2016 at 16:32 | comment | added | RubberDuck | Thank god there's a pragmatist answering this question. I thought I was going to have to write this answer. ++ | |
| Aug 16, 2016 at 15:37 | comment | added | Monty Harder | > All Toyata cars have a VIN that starts "TO" That just isn't true. They start with "JT" if made in Japan. American-built Toyotas have completely different VINs en.wikibooks.org/wiki/… | |
| Aug 16, 2016 at 8:57 | history | answered | mcottle | CC BY-SA 3.0 |