THE KE Y TO GOOD RELATIONS
For a row to be able to uniquely represent a
particular set of data, it must be unique for
the entire set of row/column value intersections within the table. If the company
using the EMPLOYEE table were to hire
another employee named Frances Newton
on September 14, 2005, and Frances Newton
had no MANAGER value associated with her
row in the EMPLO YEE table yet, the original
entry for Frances Newton would no longer be
unique. This coincidence of identical data is
referred to as a duplicate. Duplicate entries
should not be allowed in tables (more in subsequent articles on why this is so). Therefore,
the EMPLO YEE table requires a column that
will ensure uniqueness for every row, even if
the company hires several employees with
the same names and employment details.
Enter the primary key. The primary key is a
column that ensures uniqueness for every row
in a table. When a primary key is added to the
EMPLOYEE table, the two Frances Newtons
are no longer alike, because one now has
an EMPLOYEE_ID value of 37 and the other
has an EMPLOYEE_ID value of 73. Figure 2
illustrates the addition of the EMPLOYEE_ID
primary key to the EMPLOYEE table.
Note that the EMPLOYEE_ID value
appears to have nothing specifically to do
with the rest of the column/row combination values that follow it within each row.
In other words, it has nothing to do with
the employee data per se. This type of key
is often a system-generated sequential
number, and because it has nothing to do
with the rest of the data elements in the
table, it is referred to as a synthetic or
surrogate key. Using such a key is advantageous
in maintaining the uniqueness of each row,
because the key is not subject to updates
and is therefore immutable. It is best to
avoid primary key values that are subject to
changes, because they result in complexity
that is almost impossible to manage.
A table can have only one primary key,
comprising one or several columns. A
key comprising more than one column is
referred to as a composite or concatenated
key. In some cases, a primary key may not
be necessary or even appropriate. In most
cases, however, it is strongly recommended
that every table have a primary key. (Oracle
Employee_ID
37
73
1234
First_Name
Frances
Frances
Donald
Last_Name
Newton
Newton
Newton
Hire_Date
14-SEP-2005
14-SEP-2005
24-SEP-2006
Manager
28
PRIMARY KE Y
Figure 2: The EMPLOYEE_ID primary key
Employee_ID
37
1234
EMPLOYEE
First_Name
Frances
Donald
Last_Name
Newton
Newton
Hire_Date
14-SEP-2005
24-SEP-2006 28
Manager
Department ID
10
10
FOREIGN KE Y
DEPARTMENT
Department ID
10
20
Name
Accounting
Payroll
Figure 3: EMPLO YEE and DEPAR TMEN T tables with a foreign key relationship
Employee_
ID
37
First_
Name
Frances
Last_
Name
Newton
Hire_Date Manager Dept ID
Office_ 1 Office_ 2 Office_ 3
14-SEP-
2005
24-SEP-
2006
28
10
New York
Colorado
Springs
San
Francisco
San
Francisco
1234
Donald
Newton
10
New York
Figure 4: EMPLO YEE table with office location columns—first normal form violation
Database does not require every table to
have a primary key, however.)
CAREFUL CONSIDERATION OF
FOREIGN RELATIONS
Up to now, this discussion has focused on
how data is organized in a single table. But
a relational database also connects (relates)
tables and organizes information across
multiple tables.
An important connector in a relational
database is the foreign key, which identifies
a column or set of columns in one table that
refers to a column or set of columns in another
table. In addition to connecting information,
the use of foreign key relations between tables
helps keep information organized.
For example, if you store the department
name of each employee alongside each
employee’s details in the EMPLOYEE table,
you may very well see the same department
name repeated across multiple employee
listings. And any change to a department
name would require that name to subse-
quently be updated in every row for every
employee in that department.
