StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It appears that no one has yet said anything in support of non-surrogate (I hesitate to say "natural") keys. So here goes...</p> <p>A <strong>disadvantage</strong> of surrogate keys is that they are <strong>meaningless</strong> (cited as an advantage by some, but...). This sometimes forces you to join a lot more tables into your query than should really be necessary. Compare:</p> <pre><code>select sum(t.hours) from timesheets t where t.dept_code = 'HR' and t.status = 'VALID' and t.project_code = 'MYPROJECT' and t.task = 'BUILD'; </code></pre> <p>against:</p> <pre><code>select sum(t.hours) from timesheets t join departents d on d.dept_id = t.dept_id join timesheet_statuses s on s.status_id = t.status_id join projects p on p.project_id = t.project_id join tasks k on k.task_id = t.task_id where d.dept_code = 'HR' and s.status = 'VALID' and p.project_code = 'MYPROJECT' and k.task_code = 'BUILD'; </code></pre> <p>Unless anyone seriously thinks the following is a good idea?:</p> <pre><code>select sum(t.hours) from timesheets t where t.dept_id = 34394 and t.status_id = 89 and t.project_id = 1253 and t.task_id = 77; </code></pre> <p>"But" someone will say, "what happens when the code for MYPROJECT or VALID or HR changes?" To which my answer would be: "why would you <strong>need</strong> to change it?" These aren't "natural" keys in the sense that some outside body is going to legislate that henceforth 'VALID' should be re-coded as 'GOOD'. Only a small percentage of "natural" keys really fall into that category - SSN and Zip code being the usual examples. I would definitely use a meaningless numeric key for tables like Person, Address - but not for <strong>everything</strong>, which for some reason most people here seem to advocate.</p> <p>See also: <a href="https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables/337716#337716">my answer to another question</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload