Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Often in a table that has no natural key, it is still useful for users to be able to have a uniquely generated identifier. If the table has a surrogate primary key (and in such a case you would certainly expect it to) should that key be exposed to the user or should another field be used for that purpose?

One reason not to expose the surrogate key is that now you can't do operations that preserve the relationship between records, but change the key values, such as certain kinds of deletion/re-insertion, many methods of copying data from one database to another, etc.

The main advantage of exposing the surrogate key is the simplicity of using a field you have anyway.

Under what circumstances is it better to directly expose the surrogate key to users?

share|improve this question
    
Part of this question is that you need to define 'users.' Users could be consumers of an API you expose or fleshy human beings. The answer is not going to be the same for both. –  James Snell Jul 12 '13 at 22:05
    
Fleshy human beings. –  psr Jul 16 '13 at 2:11

7 Answers 7

up vote 7 down vote accepted

You need to be ready for any identifier that is exposed to users/customers needing to be changed, and changing the identity of a row in a database and propagating that change to all foreign keys is just asking to break data.

If the data has no natural business key, you can add an additional field for a "business identifier". This should be optimized for the processes it is used for. Telephone keypad entry means numeric only. Over the phone/verbal means avoid similar sounding symbols (B/D, M/N, etc). You can even autogenerate some easily memorable phrase ("green jelly").

The effect of this is that the business can later change how they want to refer to records, and the only data schema change is either adding a new column for that style of id or transform the ids already there. The change doesn't propagate through the entire database, and you still have one id (the surrogate) that is valid over time.

EDIT:

To more directly answer the question, I would avoid exposing surrogate keys to users. As the comments point out, surrogate keys should almost never change. Conversely, businesses want to change everything. If the surrogate key is exposed, it is just a matter of time before the business wants to change it.

As a side note, when I say "exposing" here, I mean to give the key to the user with the expectation that they use it directly (like calling in to support with their order number).

share|improve this answer
2  
This answer makes no sense. Surrogate keys never change, and you haven't made a case for not exposing them to users. –  Robert Harvey Jul 10 '13 at 23:48
2  
@DougM: Then write the records to a new consolidated table with it's own surrogate key, and maintain the original keys in separate fields in the new table (and a field that identifies the original source table) as references, if needed. That kind of consolidation should be extraordinarily rare, and only as a result of a botched design. Repeat after me: Surrogate. Keys. Never. Change. That's why they're surrogates. –  Robert Harvey Jul 11 '13 at 4:55
1  
@RobertHarvey I agree that surrogate keys should never change, which is why I think they make poor external identifiers. Eventually a customer will want to change how they refer to a record. At that point you have either built your entire system around immutable surrogate keys or you thought ahead and put a level of indirection between business identifiers and the surrogate keys. –  Chris Pitman Jul 11 '13 at 5:34
1  
@RobertHarvey And I do not disagree, the surrogate key should be used for all relationships within the database. A business id value is only useful for identifying the record to users/humans external to the system. –  Chris Pitman Jul 11 '13 at 16:15
2  
@sqlvogel: Would it make things clearer if I used the term "system-generated primary key" instead of "surrogate?" I do agree that you might want to change the algorithm that generates the surrogate keys, but that doesn't change their fundamentally immutable quality. –  Robert Harvey Jul 11 '13 at 19:11

In some cases, surrogate keys are expected and make sense to users. My favorite example is "order number". Order number isn't really a natural key: a natural key might be timestamp plus user, or maybe more than that if you expect users to generate more than one order within the granularity of your timestamp.

Nonetheless, users understand and expect the convenience of an order number. There is no harm, and lots of value, if you let users know about them.

On the other hand, some surrogate keys make no sense to a user. Sure, my health insurance company has some surrogate key that identifies me based on my member id, date of birth, carrier, etc, but I don't care about that, I care about the info on my card (which often includes ids based on my employer and are not unique across the universe... Hence the surrogate key at the insurance company).

share|improve this answer

If a table has no natural key, surrogate keys allow rows like this.

surrogate_key  some_name
--
1              Wibble
2              Wibble
...
17             Wibble
...
235            Wibble

I'd call these artificial keys instead of surrogate keys, but that distinction isn't important for this question.

Now, assuming that there's important data referencing these surrogate keys through foreign keys, how would the end users know which row to update if they don't know the surrogate key values?

share|improve this answer

In layman's words:

  • Surrogates should be hidden from the user.
  • You should expose some other business candidate key to the user.
  • If no other candidate key exist you should show the PK. But in this case the PK is not considered a surrogate since it's not a substitute for other column.
share|improve this answer
    
Why must the PK be shown? I think that is my question - whether an auto generated PK is properly called a surrogate key is tangential. –  psr Jul 10 '13 at 22:55
1  
@psr Your question title says "surrogate keys ever be exposed". I said no. But you have to show some other key. If no other key exist then you must show the only key you have. Tangentially I clarify that in those cases the key is not really a surrogate because it's not a substitute for any other column. –  user61852 Jul 10 '13 at 23:03
    
The question is whether you should add a column, or show the key you have. –  psr Jul 10 '13 at 23:03
    
@psr I re-phrased my answer to make it more clear. –  user61852 Jul 10 '13 at 23:06
1  
I find that to be a materially insignificant distinction. If your rule is that every table has an artificial key (which mine is), and that only the artificial keys participate in joins (which is my principle as well), then the notion that a key is a surrogate because other keys might possibly be available is inconsequential. –  Robert Harvey Jul 11 '13 at 0:41

you should ONLY expose a field to a user that provides useful information to the user, either directly or in reporting defects to you.

conversely, you should ALWAYS expose "surrogate primary keys" when they are the principal means of identifying a record (simple or complex) for an interaction the user performs.

share|improve this answer

It shouldn't matter whether you expose the keys or not to the end user. Your application should perform the necessary authorization such that simply knowing an order id, for example, can't allow them access to something they normally wouldn't have access to.

caveat: this assumes a web based or n-tier application where server side authorization is possible/feasable. If you have a VB app thats directly executing sql, thats a whole 'nother issue.

share|improve this answer
    
What about not be able to insert then delete records, while preserving foreign key relationships, as mentioned in the question? –  psr Jul 12 '13 at 21:33
    
What does that have to do with exposing the key to the user? Maybe I dont understand your use of the term 'exposing'. I'm working from the assumption you mean 'able to be seen by the user'. –  GrandmasterB Jul 12 '13 at 22:04

You should only expose a surrogate key if it's a properly generated GUID/UUID*. Exposing sequential surrogate keys is number 4 on the OWASP Top 10 security issues.

* In practice, it's best to assume that it wasn't properly generated for these purposes unless you know that it was created by a cryptographically secure random or pseudo-random number generator.

share|improve this answer
2  
It is unclear from your answer how a GUID improves security. –  Robert Harvey Jul 11 '13 at 17:55
1  
@RobertHarvey - I assume because they aren't sequential and so can't be guessed. –  Bobson Jul 11 '13 at 17:56
1  
    
@RobertHarvey, clarified. –  Peter Taylor Jul 11 '13 at 18:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.