Java Reference
In-Depth Information
Primary Keys
A primary key is a column that uniquely identifies the rest of the data in any given row. For example, in
the Customers Table, the Customer_Number column uniquely identifies that customer. For this to work,
no two rows can have the same key (or, in this instance, Customer_Number), so a key is a good
example of the use of the UNIQUE constraint. A clear benefit of using a unique integer as a row
identifier is that a list of integers is far faster to search than an array of First Name/Last Name
character variables. Another obvious benefit of using unique keys is that your system can support
more than one customer with the same name, as the Customer_Number is your primary means of
identifying customers.
Foreign Keys
A foreign key is a column in a table where that column is a primary key of another table. For example,
the Orders Table contains one column for Order_Number, which is its own primary key, and another
column for Customer_Number, which is a foreign key.
The purpose of these keys is to establish relationships across tables, without having to repeat data in
every table. This concept encapsulates the power of relational databases. You see many examples of
the use of both primary keys and foreign keys in the Joins you work with in this chapter.
Using Inner Joins
An Inner Join between two or more tables, as discussed, represents the intersection of the sets of keys
matching some query. The most common form of query used in creating an Inner Join involves the
selection of rows that have a key equal to some particular value. A typical example might be to find
data from a number of tables where the Customer_ID equals that of a specific customer. Joins using
this equality test are called Equi-Joins, and are discussed in the next section .
Using Equi-Joins
SQL Joins work by matching up equivalent columns in different tables by comparing keys. The most
common type of Join is an Equi-Join , where you look for items in one table that have the same item
number as items in another. The first example demonstrates how Equi-Joins work.
The examples throughout Part II have used variations on the Customers table shown in Table 9-1 and
the Inventory table shown in Table 9-2 . These tables form the basis of an order management
database.
Table 9-1: Customer Table
Customer_Numb
er
First_Nam
e
M
I
Last_Nam
e
Street
City
Stat
e
Zip
100
Michael
A
Corleone
123
Pine
New
York
NY
1000
6
101
Fredo
X
Corleone
17
New
NY
1000
 
Search WWH ::




Custom Search