Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are 'artificial'.

However, it sometimes seems to be a matter of policy to have an auto-incrementing integer field as the primary key of each table. Is this always the best idea to have such a single-field key and why (or why not)?

--edit

To be clear, this question is not about artificial vs natural - but about whether all artificial keys should be single-field

share|improve this question
    
in a data warehouse dimension table, yes. in an operational database, sometimes. –  Neil McGuigan Mar 20 '13 at 5:58

3 Answers 3

"it depends"

Yes: Surrogate IDENTITY/AUTONUMBER fields are good when the natural key is wide and non-numeric. Note: this assumes the conflation of "PK" and clustered index that occurs by default in SQL Server and Sybase etc

No: many/many tables when the 2 parent keys suffice. Or when the natural key is short and fixed length eg currency code

Of course, a braindead ORM (read: (n)Hibernate) may trump these rules...

Edit: reading question again

A many/many table with 2 surrogate parent keys will have a multiple column PK.
However, it doesn't need another surrogate column.

If a table does have a surrogate (IDENTITY etc) key then it doesn't need to be multiple column.

You can have a super key that includes the surrogate but this would be to enforce other rules (eg subtypes)

share|improve this answer
2  
+1 for ORMs (or other practical considerations) can pollute your purist model. –  Joel Brown Sep 24 '11 at 12:22
    
Couldn't agree with you more about your ORM comment. Seems like developers are just trying to get away from the specifics of database design and the inner works, allowing these models to make a lot of assumptions, often times at the cost of performance and common sense. –  Thomas Stringer Sep 24 '11 at 17:28
    
@Surfer513 - Understandably. A developer will want as much as possible to focus on the "meat", which for them is the application. Hence the trend towards cloud-based services (e.g. AWS, SQL Azure, Heroku), ORM, and so forth. –  Nick Chammas Sep 24 '11 at 19:09
2  
@Nick I am a developer and I for one love the inner workings of RDBMS's. Take for instance Linq to SQL. Huge performance hit as it makes needless calls to the db. I will stick with direct database design/dev every time. I feel it is too significant to abstract. –  Thomas Stringer Sep 24 '11 at 20:17

I'm going to say no, not always, but most of the time yes..

These are some circumstances in which you don't need a surrogate or artificial key:

  • Pure intersection tables. If there is no risk of the intersection being the target of a foreign key and if there is little or no risk of the intersection attracting independent attributes (i.e. something other than FK's to the two parent tables) then you can get away with using the combination of FKs as the PK with fair confidence.
  • Lookup tables with static business keys. If you have a lookup
    table with a unique business key which is fixed externally to your
    business and which has zero chance of ever changing for any
    practical purpose, then using the business key directly can make
    things simpler. An example might be a list of state or province
    codes or a list of ANSI standard numbers, etc.
  • Tables containing data consolidated from multiple, independent sources. If your system has many sources of data that must be shoehorned together into a single table, say at head office, then sometimes you need a compound key that includes the source system key value and a code indicating what the source system was.

There are also some situations where the old-faithful monotonically increasing integer surrogate key is not ideal. You can have keys that are alphanumeric surrogates. These could include:

  • Situations where you need to merge data from multiple, independent sources. To avoid key collisions you might use GUIDs instead of IDENTITY keys.
  • Situations where you are forced to use non-numeric key representations. Let's say you've got a license plate database. Your key could be the alphanumeric value instead of a pure number.
  • Situations where some external requirement forces you to apply compression to your key value. Instead of using 10 digits for an int32 you can use six base 36 digits.

Why most of the time yes? The most fundamental answer to that question is that it is pure hell if you ever need to modify a primary key value on any table. Since almost anything a user can see or touch is conceivably subject to an update at some point, using a visible key value is inviting pure hell. Using a surrogate key will keep you from falling into this trap.

Having said that, remember that there is room for YAGNI in applying this concept. You don't need to go forcing code tables with IDENTITY keys into every nook and cranny of your schema, just in case someone decides that the symbol for male gender in your employee table needs to change from M to X or something silly.

share|improve this answer
    
+1 great and thorough answer with awesome examples –  Thomas Stringer Sep 24 '11 at 1:54
    
"Using a surrogate key will keep you from falling into this trap" The question is not about surrogate vs natural, but about single-field vs multi-field surrogates. –  Jack Douglas Sep 24 '11 at 12:04
    
As you've acknowledged in a comment to your own answer, we are in "agree to disagree" territory when it comes to what is prudent in database design. Given your edit I would not have made the distinction between surrogate and natural keys, so my second bullet point is belatedly off topic. My other points regarding when one might diverge from the classical identity/sequence approach still stand. –  Joel Brown Sep 24 '11 at 12:19
    
I didn't change the question as you can see from the history, just added emphasis where I thought it would help skim-readers –  Jack Douglas Sep 24 '11 at 14:50
    
+1 great answer. Both surrogates and naturals are good. In some tables a natural key applies, in others a surrogate key applies. –  user1598390 Jul 9 '13 at 19:51
up vote 6 down vote accepted

No.

I'd say there are certainly cases when single-field keys are inferior to compound keys, at least for the purpose of foreign keys. That is not to say you should not have a single-field surrogate key as well if you prefer, but I personally prefer the key that is most often used as the target of a foreign key to be called the primary key

I'll attempt to illustrate my point in the the following examples, in which:

  • brand is car marque, eg Ford, Toyota etc
  • dealer is a physical dealership, tied to a brand (eg a Ford dealership only selling Fords)
  • model is the type of car eg Ford Focus, Ford Fiesta etc
  • stock is the current forecourt car count for each dealership

If we create a single-field surrogate key for dealer and model as follows:

create table brand( brand_id integer primary key );

create table dealer( dealer_id integer primary key, 
                     brand_id integer references brand )

create table model( model_id integer primary key, 
                    brand_id integer references brand )

create table stock( model_id integer references model, 
                    dealer_id integer references dealer, 
                    quantity integer,
                      primary key(model_id, dealer_id) )

then it is possible to insert a row into stock that links a Ford dealer to a "Toyota" model. Adding brand_id references brand to stock only makes the problem worse. On the other hand if we keep the foreign key as part of the primary key as follows:

create table brand( brand_id integer primary key );

create table dealer( brand_id integer references brand, 
                     dealer_id integer, 
                       primary key(brand_id, dealer_id) )

create table model( brand_id integer references brand, 
                    model_id integer, 
                      primary key(brand_id, model_id) )

create table stock( brand_id integer, 
                    model_id integer, 
                    dealer_id integer, 
                    quantity integer,
                      primary key(brand_id, model_id, dealer_id),
                      foreign key(brand_id, model_id) references model,
                      foreign key(brand_id, dealer_id) references dealer )

now the rule that "Ford" dealers can only stock "Ford" cars is enforced naturally by the model.

Note that in the 'composite keys' example, dealer_id may or may not be unique, according to preference. It does not need to be unique (ie an alternate key), but very little is lost by making it so (perhaps a little storage space) and it can be very handy so that is the way I usually set it up, eg:

create table dealer( brand_id integer references brand, 
                     dealer_id serial unique, 
                       primary key(brand_id, dealer_id) )
share|improve this answer
3  
Making allowances for the usual provisos about examples not necessarily being perfect from every angle, I would say that this type of design is particularly brittle. While there's something satisfying about finding a way to use DRI to enforce business rules, it also takes away some of your ability to respond to changes. If Toyota buys Ford, or even if a Ford dealer decides to sell a used Toyota, your DRI-driven business rules are going to give you a maintenance headache. –  Joel Brown Sep 24 '11 at 1:29
    
So "your business rules might change". That is true of any business rule and is always going to be potentially difficult to re-model. I usually get told what the business rules are - I don't decide them for myself. –  Jack Douglas Sep 24 '11 at 5:45
    
Are you saying DRI should not be used at all to enforce business rules? Isn't that the only thing DRI does? - even simple foreign keys are business rules enforced by DRI. –  Jack Douglas Sep 24 '11 at 9:58
3  
Of course DRI enforces business rules. You have to decide which rules are going to be enforced in code and which in your schema. Schema changes are almost always harder than code changes. There are two kinds of business rules that can go into your data model. One belongs there and one doesn't. The fundamental nature of the data that is important to your business doesn't change very much. The specific ways in which you operate on that data is much more volatile. A rule like cars have a maker belongs in the data model. A rule like dealers will never sell two brands of cars doesn't. –  Joel Brown Sep 24 '11 at 11:48
3  
"Schema changes are almost always harder than code changes" IMO the opposite is true. Actually I disagree with virtually everything you just said but I doubt there is any point arguing the toss with you, so I'll leave it at that. –  Jack Douglas Sep 24 '11 at 12:03

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.