Datasheet

Keys
Within database tables it is often important to uniquely identify rows, especially when defining relation-
ships. The position of a row isn’t enough here, because rows may be inserted or deleted, so any row’s
position might change. The order of rows is also an ambiguous concept because rows may be ordered
by one or more columns in a way that varies depending on how you are using the data in the table —
this is not a fixed definition. Also, the data in a single column of a table may not be enough to uniquely
identify a row. At first glance, you might think that the
EntryName column in the PhoneBookEntry
table example could uniquely identify a row, but there is no guarantee that the values in this column
will be unique. I’m sure there are plenty of Karli Watsons out there, but only one of them is writing this
book. Similarly,
PhoneNumber may not be unique because people in families or student housing often
share one phone. And a combination of these fields is no good, either. While it is probably quite unlikely
that two people with the same name share a phone, it is certainly not unheard of.
Without being able to identify a row by either its contents or its position, you are left with only one
option — to add an additional column of data. By guaranteeing that every row includes a unique value
in this column, you’ll always be able to find a particular row when you need to. The row that you would
add here is called a primary key, and is often referred to as the PK or ID of the row. Again, naming con-
ventions vary, but in this book all primary keys end with the suffix
Id.
Graphically, the primary key of a table is shown with a key symbol. Figure 1-2 shows a modified version
of
PhoneBookEntry containing a primary key.
Figure 1-2: The PhoneBookEntry table
with a primary key
The data type used here is
uniqueidentifier, which is in fact a GUID (Globally Unique IDentifier). It
is not mandatory to use this data type, but it is a good thing to use. There are many reasons for this,
including the fact that GUIDs are guaranteed to be unique (in all normal circumstances). Other typically
seen types for primary keys include integer values and strings.
It is not always absolutely necessary to define a new column for primary key data. Sometimes the table
contains a column that is unique by definition — a person’s Social Security number for example. In some
situations, combining two columns will give a unique value, in which case it is possible to use them to
define a compound primary key. One example of this would be the combination of postal code and
house number in a table of U.K. addresses. However, it is good practice to add a separate primary key
anyway, and in this book most tables use
uniqueidentifier primary keys.
Relationships
RDBMSes are capable of defining relationships between tables, whereby records in one table are associ-
ated (linked) with records in other tables. When storing large quantities of data, this relational aspect is
5
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:30 PM Page 5