Database Reference
In-Depth Information
Alternatively, if you define the surrogate key DepartmentID as the primary key
of DEPARTMENT, then DepartmentID will also be the foreign key in EMPLOYEE. When
you retrieve a row of EMPLOYEE, you will get back a number such as 123499788 for the
DepartmentID, a value that has no meaning to you at all. You have to perform a second query
on DEPARTMENT to obtain DepartmentName.
The second disadvantage of surrogate keys arises when data are shared among different
databases. Suppose, for example, that a company maintains three different SALES databases,
one for each of three different product lines. Assume that each of these databases has a table
called SALES_ORDER that has a surrogate key called ID. The DBMS assigns values to IDs so
they are unique within a particular database. It does not, however, assign ID values so they
are unique across the three different databases. Thus, it is possible for two different SALES_
ORDER rows, in two different databases, to have the same ID value.
This duplication is not a problem until data from the different databases are merged.
When that happens, to prevent duplicates, ID values will need to be changed. However, if ID
values are changed, then foreign key values may need to be changed as well, and the result is a
mess, or at least much work to prevent a mess.
It is, of course, possible to construct a scheme using different starting values for surrogate
keys in different databases. Such a policy ensures that each database has its own range of
surrogate key values. This requires careful management and procedures, however; and if the
starting values are too close to one another, the ranges will overlap and duplicate surrogate
key values will result.
By The Way Some database designers take the position that, for consistency, if one
table has a surrogate key, all of the tables in the database should have a
surrogate key. Others think that such a policy is too rigid; after all, there are good data
keys, such as ProductSKU (which would use SKU codes discussed in Chapter 2). If
such a key exists, it should be used instead of a surrogate key. Your organization may
have standards on this issue that you should follow.
Be aware that DBMS products vary in their support for surrogate keys. Microsoft
Access 2013, Microsoft SQL Server 2012, and MySQL 5.6 provide them. Microsoft SQL
Server 2012 allows the designer to pick the starting value and increment of the key, and
MySQL 5.6 allows the designer to pick the starting value. Oracle Database 11 g Release
2, however, does not provide direct support for surrogate keys, but you can obtain the
essence of them in a rather backhanded way, as discussed in Chapter 10B.
We use surrogate keys unless there is some strong reason not to. In addition to the
advantages described here, the fact that they are fixed simplifies the enforcement of
minimum cardinality, as you will learn in the last section of this chapter.
Specifying Candidate (alternate) Keys
The next step in creating a table is to specify candidate keys. As discussed in Chapter 3,
candidate keys are alternative identifiers of unique rows in a table. Some products use the
term alternate key (AK) rather than candidate key, but the two terms are synonymous.
Figure 6-3 illustrates the use of alternate keys, using alternate key (AK) notation.
EMPLOYEE
CUSTOMER
Figure 6-3
Representing Candidate
(Alternative) Keys
EmployeeNumber
CustomerNumber
EmployeeName
Phone
Email (AK1.1)
HireDate
ReviewDate
EmpCode
Name (AK1.1)
City (AK1.2)
Phone
Email (AK2.1)
(a)
(b)
 
Search WWH ::




Custom Search