Timeline for Is it good practice to always have an autoincrement integer primary key?
Current License: CC BY-SA 3.0
43 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 28, 2016 at 22:02 | comment | added | Martin Schröder | See also Should every table have a single-field surrogate/artificial primary key? | |
| Aug 21, 2016 at 22:34 | answer | added | Pieter Geerkens | timeline score: 2 | |
| Aug 21, 2016 at 4:20 | comment | added | The Nate | Cross tables: Why would you not simply use a concatenated key? What would the advantage be for an auto inc. int. thence? | |
| Aug 19, 2016 at 19:55 | comment | added | Mr Anderson | One advantage to auto-increment Ids that has not been mentioned: provided the Id values are always non-negative, the items can easily be indexed using an array structure. Otherwise, one must use a hashtable or other less efficient structure. | |
| Aug 19, 2016 at 2:13 | answer | added | Archimedes Trajano | timeline score: -3 | |
| Aug 18, 2016 at 2:35 | answer | added | Zenilogix | timeline score: 2 | |
| Aug 17, 2016 at 18:25 | comment | added | oopexpert | If the table consists only of relations to other entities, there is no need for an autoincrement value. As soon as you add a mutable (primitive) element your record should contain a unique key. Primitive is in Brackets because a primitive can become a relation if you expect records to be indistinguishable from each other if they define the same relations AND primitive values. But that is an issue of design. If you introduce ORM then you might face the problem that even relations need a unique autoincrement id. But that is an configuration and design issue again. | |
| Aug 17, 2016 at 16:57 | comment | added | brian_o | "Is it good practice to always..." No. | |
| Aug 17, 2016 at 14:10 | comment | added | Hannah Vernon | You may want to check my question and the answers on this subject at dba.stackexchange.com/questions/50708/… | |
| Aug 17, 2016 at 7:28 | answer | added | miroxlav | timeline score: 12 | |
| Aug 16, 2016 at 23:29 | comment | added | Bergi | @MatthewWhited It's not just about competitors. It also could be a privacy issue or something else. I'm not saying it often is an important issue, you just should give it a quick thought and then decide that it doesn't matter in your threat scenarios. | |
| Aug 16, 2016 at 23:20 | answer | added | Loren Pechtel | timeline score: -2 | |
| Aug 16, 2016 at 21:04 | comment | added | Mark Rogers | Guids, anyone? lol | |
| Aug 16, 2016 at 20:58 | history | protected | gnat | ||
| Aug 16, 2016 at 20:53 | comment | added | GrandmasterB | Regarding the German Tank Problem, I generally use an additional guid for resources where I don't want the ID visible. | |
| Aug 16, 2016 at 20:11 | comment | added | Matthew Whited | As another note, if you are worried about your competitors knowing how fast you grow then you have a bigger issue of not worrying about your own growth. "we would postulate" is another name for "we have no idea but for giggles we will make something up for marketing reasons" | |
| Aug 16, 2016 at 20:09 | answer | added | anw | timeline score: 8 | |
| Aug 16, 2016 at 20:05 | comment | added | Matthew Whited | And, that information doesnt really give you anything useful other than to know how fast data may be created. But it's just an estimate and it provides nothing useful to anyone other than a blind guess it some random correlation may almost mean something. | |
| Aug 16, 2016 at 19:55 | comment | added | Chris Hayes | @MatthewWhited It's not just about swapping parameters in a URL. Suppose you use a site and you create asset 100 at time t, and asset 120 at time t + 60. If you can see both of those IDs (100 and 120) in unobfuscated form, you now know the total number of assets which exist, as well as roughly the rate at which they're created. This is information leakage. This is not purely hypothetical. | |
| Aug 16, 2016 at 18:55 | comment | added | Matthew Whited | To everyone complaining about the "german tank problem".... if the only thing keeping someone from accessing data they shouldn't is a key in your URL... you have bigger problems than GUID versus Auto INT. | |
| Aug 16, 2016 at 18:22 | answer | added | Bradley Thomas | timeline score: 12 | |
| Aug 16, 2016 at 17:12 | comment | added | davidbak | Not just as part of a join - perhaps it is part of a data reporting system. E.g., you take your current system which works for a department and replicate it so that there is an independent copy in each department. Then, after that, you discover a need to have a data warehouse collecting information from all departments. At that point, your auto-increment keys collide. Or, consider a system that grows so much you need to shard it. You'd like them to run independently (no temporal interdependency) but auto-increment keys will collide. @jamesgifford is right - the situation is general. | |
| Aug 16, 2016 at 15:40 | answer | added | Pedro Werneck | timeline score: -1 | |
| Aug 16, 2016 at 15:19 | answer | added | KeithS | timeline score: 8 | |
| Aug 16, 2016 at 11:12 | answer | added | Three Value Logic | timeline score: 5 | |
| Aug 16, 2016 at 10:12 | review | Close votes | |||
| Aug 20, 2016 at 2:21 | |||||
| Aug 16, 2016 at 9:52 | answer | added | AnoE | timeline score: 7 | |
| Aug 16, 2016 at 8:57 | answer | added | mcottle | timeline score: 22 | |
| Aug 16, 2016 at 8:44 | comment | added | Luaan | Do you mean primary key or clustered index? The two are very different, though they may be defined over the same columns by default. | |
| Aug 16, 2016 at 6:08 | answer | added | Drathier | timeline score: 117 | |
| Aug 16, 2016 at 1:43 | history | tweeted | twitter.com/StackProgrammer/status/765363356308037632 | ||
| Aug 16, 2016 at 1:18 | comment | added | AJJ | @jamesgifford But wouldn't I likely be merging with that ID as part of the join? Joining tables where for example thistable.profile_id = profile_table.id | |
| Aug 16, 2016 at 0:59 | comment | added | Rorshark | Also consider the case where you find yourself needing to merge two tables. Easy to do with globally unique ids. Hard with auto-incrementing ints. | |
| Aug 16, 2016 at 0:50 | answer | added | MatthewToday | timeline score: 7 | |
| Aug 16, 2016 at 0:24 | comment | added | Joshua Taylor | @ArukaJ The point is that it leaks some information about the system. E.g., suppose the database contains user-written posts, each of which gets a sequential id. Say you make four posts, each of which gets an id: at 4am (20), 5am (25), 8pm (100), and 9pm (200). By looking at the ids, you can see that only 5 posts were added between 4am and 5am, while 100 were added between 8pm and 9pm. If you were trying to pick the time for a denial of service attack, that could be valuable information. | |
| Aug 15, 2016 at 23:50 | comment | added | AJJ | @Bergi I can't tell if this is a joke or an analogy for some other form of SQL issue? | |
| Aug 15, 2016 at 23:40 | comment | added | Bergi | Have a look at the German tank problem for an example where a plain auto-incrementing identifier is a problem. Of course this only matters if you are using your ids in public. | |
| Aug 15, 2016 at 22:20 | answer | added | Tulains Córdova | timeline score: 65 | |
| Aug 15, 2016 at 21:08 | comment | added | TZHX | Being able to uniquely reference a row is useful, just having an id for the sake of having it on something that's just a link table between profile and subscription isn't necessarily the best way to force this, when there's other ways (for example a unique constraint/index, or the primary key being a composite of the two values) which can have benefits in the storage engine that then help queries perform better. | |
| Aug 15, 2016 at 20:58 | vote | accept | AJJ | ||
| Aug 15, 2016 at 20:51 | answer | added | misterbiscuit | timeline score: -2 | |
| Aug 15, 2016 at 20:47 | answer | added | GrandmasterB | timeline score: 156 | |
| Aug 15, 2016 at 20:35 | history | asked | AJJ | CC BY-SA 3.0 |