Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. POSurrogate vs. natural/business keys
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USTony Andrews
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. CO-1 Natural keys as a primary key have the problem that for every child table you have to add the parent's key which can be composed by more than one field (instead of only one which is the case of a surrogate key) and also the child key. So imagine the following where starting from TABLEA the relationship is 1-0..*: TABLEA PK: ID_A TABLEB PK: ID_A ID_B TABLEC PK: ID_A ID_B ID_C TABLED PK: ID_A ID_B ID_C ID_D. See the problem? The parent key is propagated in the children tables. What would happen if the primary key of TABLEA changes? Now you would have to refactor all the child tables PK too.
      singulars
      1. PO
      1. USAlfredo Osorio
    2. CO@Alfredo: yes of course there is a trade-off. However, in my 20+ years of experience I have rarely seen the definition of a table's PK change. If it happened on a regular basis I'd probably avoid natural keys too. In reality, on the extremely rare occasions that this happens I'm prepared to take the hit of the extended impact.
      singulars
      1. PO
      1. USTony Andrews
    3. COThis is a very reasonable answer. For example, I'm currently trying to design a state-machine schema, and I have the option of going with either a `UNIQUEIDENTIFIER` or a simple `VARCHAR`. In the end, which one is more readable? `SELECT ... FROM dbo.StateMachine WHERE id = '21556f00-9896-4455-ba26-cadea386d3cd'`, or `... WHERE id = 'registration'`? Even if you call them "natural keys", a lot of them end up being technical identification keys that just happen to be convenient.
      singulars
      1. PO
      1. USvoithos
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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