Datasheet

Part I SQL Basic Concepts and Principles
warn you if you create a table without defining a primary key. Some purists go even further, spec-
ifying that the primary key should be
meaningless in the sense that they would use some gener-
ated unique value (such as
EMPLOYEE ID) instead of, say, Social Security numbers (despite that
these are unique as well).
A primary key could consist of one or more columns — although some fields may contain duplicate
values, their combination (set) is unique through the entire table. A key that consists of several
columns is called a composite key.
Although the primary key is a cornerstone for defining relationships in RDBMS, the
actual implementations (especially early ones) have not always provided built-in sup-
port for this logical concept. In practice, the task of enforcing uniqueness of a chosen primary key
was the responsibility of programmers (requiring them to check for existing values before
inserting new records, for example). Today, all major relational database products have built-in
support for primary keys — on a very basic level, this means that the database does its own
checking for unique constraint violations and will raise an error whenever an attempt to insert a
duplicate record is made. Chapter 4, ‘‘Creating RDMS Objects,’’ covers this topic in more detail.
Foreign key
Let’s go back to the CUSTOMER and ORDER HEADER tables. By now, you should understand why
the
CUST ID N was designated as a primary key — it a has unique value, no customer can pos-
sibly have more than one ID, and no ID could be assigned to more than one customer. To track
what customers placed which orders, you need something that will provide a link between cus-
tomers and their orders.
The
ORDER HEADER table has its own primary key — ORDHDR ID N, which uniquely identifies
orders. In addition to that, it will have a foreign key
ORDHDR CUSTID FN field. The values in
that field correspond to the values in the
CUST ID N primary key field for the CUSTOMER table.
Note that, unlike the primary key, the foreign key is not required to be unique — one customer
could place several orders.
Now, by looking into the
ORDER HEADER table, you can find which customers placed particu-
lar orders. The table
ORDER HEADER is related to the table CUSTOMER through the values of that
foreign key. It is easy to find a customer based on orders, or find orders for a customer. You no
longer need to know database layout, understand the order of the records in the table, or mas-
ter some low-level proprietary programming language to query data. You can now run ad hoc
queries formulated in a standard English-like language — the Structured Query Language (SQL).
Invasion of RDBMS
In spite of the clear advantages of the relational database model, it took some time for it to
become workable. One of the main reasons was the hardware. The logically clear and clean
model proved to be quite a task to implement, and even then it required much more in terms
of memory and processing power than legacy databases.
18