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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

For example we have:

Surrogate key: ABC123
Primary key: 1
Name: James

Is this legit in a Data Warehouse table?

share|improve this question
I assume you're talking about a dimension table, right? – Jon Seigel May 10 '13 at 18:06

A table can and should have as many keys as it needs. Usually when a surrogate key is used it means you will also want some alternative key as well (variously called a domain key, natural key or business key).

The practice of designating any one key as "primary" is of no great significance. It is simply a convention to mark one of the keys as "preferred" or important in some way that has meaning for the database user. Typically the primary key is the one referenced by foreign key constraints in other tables.

share|improve this answer

It depends on the purpose of the key. "Surrogate key" can mean different things to different people; to my mind, it means a candidate key (a field or combination of fields which uniquely identify a record) other than the primary key. For example, social security number may be a surrogate key for customers (not a very good one, mind).

If the values are alphanumeric as in your example, it's probably a business key. If warehouse users are likely to need this key, then sure, include it. On a fact table, if there are several such keys, and/or they're unusually wide, consider flaking them out to keep your fact table narrow.

If it's a synthetic key, a key created for use in the warehouse to avoid the many problems associated with business keys, then sure, include it. Indeed, this may well be your primary/clustering key.

I'm not sure this answers your question. Can you be a little more specific? Are you considering whether to include this field in a warehouse you're designing?

share|improve this answer

Your Answer


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.