1

I think it doesn't make sense for a table to have a surrogate key without also having a (natural) alternate key (keep in mind that one of the properties for a surrogate key is that it has no meaning outside the database environment).

For example say I have the following table:

enter image description here

Say that employee_id is the surrogate primary key, and there is no (natural) alternate key in the table.

Now let's say that some employee wants to change his phone number, how can we identify the record for this employee in the table? we can't identify it using the surrogate key, because the surrogate key is not known in the real world (i.e. we don't know the employee_id for each employee).

So there must be a (natural) alternate key in the table to identify each employee in the real world (for example: SSN).

Am I correct, or am I missing something?

Tom
  • 1,344
  • 9
  • 27
  • But in this design we would expect that employee ids are known as part of the old or new business procedures regardless of the database, hence *are* natural keys. (See my updated answer re "surrogate" & "natural".) – philipxy Nov 21 '16 at 09:14

3 Answers3

1

You are right. Normally the users of a database need to be able to map information in a database table to real concepts or things outside the database. For that they need a usable natural key - or something that can be reliably translated into a natural key.

Your specific example isn't necessarily a good one because many (most?) organisations allocate an employee identifier to employees for the duration of their employment. That employee identifier may be known and used as the natural key by both employee and employer. You have said employee_id in your example is a surrogate but based on its name many people might assume it is not.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

Every table has a candidate key. (Possibly, all the columns.) Each value of that key designates something. If employee_ids weren't used externally, they and the the set of the other three columns would both identify sets of people with the same name, address and phone number.

You are right that a table must contain a natural key for whatever entity you want to identify--for "natural key" meaning "designator under the current business rules, external to the database".

You seem to be confusing multiple meanings for "surrogate key" and "natural key".

For "surrogate": One use is, a property set where designations are determined since current business rules: surrogate as new. Another use is, a property set where designations are determined since current business rules and only used internally: surrogate as internal.

For "natural": One use is, a property set designating under current business rules and before: natural as old. Another use is, a property set designating under current business rules: natural as external.

The original use of "surrogate" was as internal with "natural" as external. Unfortunately now usually people use "surrogate" as new and "natural" as old. And they seldom either consider or distinguish surrogates as internal. Some people might call a newly introduced external designator as both surrogate (as new) and natural (as external). (Re "meaningless" names.)

All you can do is decipher or ask what someone means when they use these terms.

Note that these definitions are relative to the "current" business rules. You also seem to be assuming that employee ids arrived with the database. At some point they were introduced, so were chosen after some older system started, so were surrogates as new under the new system. But if the database came later then by that time they were natural keys as old. They were natural as external both times; when introduced they were just new natural as external.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • I would say, "every well formed table". I can't tell you the number of databases I have fixed where no primary key was declared, and where there were duplicate rows, yielding mysterious results. – Walter Mitty Nov 21 '16 at 12:51
0

The term "natural key" belongs with the subject matter or problem domain, and not with the database design or solution domain.

If you ask people in the organization how they refer to each other, it's usually by name. "That's Mary Jones, sitting by herself". You never hear, "That's employee 79932, sitting by herself."

If you ask a database person, like most of us, what's wrong with using the name, you'll get the standard answers: it's not unique. It's mutable. It's too many characters. All of that is true, but it doesn't change how people work in the real world.

The item "social security number" began life as an artificial key, even though that was before the computer revolution really began (1938). Over time, it has taken on most of the look and feel of a natural key. In your case, I would even call it a natural key, because every employee has one (barring something hokey).

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58