Accessing Databases Using JDBC - Java Programming: Comprehensive Concepts and Techniques - page 722

To Create Tables with Foreign Keys
1. Enter lines 106 through 122 as shown in Figure 11-17 on pages 710
and 711.
The UserStocks table is created containing two fields, each of which is a
foreign key to another table (Figure 11-26). The field, userID, is a foreign
key to the Users table field, also named userID, and the field, symbol, is a
foreign key to the Stocks table field, also named symbol.
foreign key
defined within
field declaration
foreign key defined
independent of
field declaration
FIGURE 11-26
The UserStocks table was created containing foreign keys to other tables.
Two different approaches are illustrated. Lines 111 and 112 define a constraint
within the userID field declaration. Line 113 defines the field, symbol, and then
lines 114 and 115 define a constraint independent of the field definition. These
two approaches allow flexibility in defining individual field constraints. Addi-
tionally, the second approach allows primary and foreign key constraints
consisting of multiple fields to be defined after all fields are declared.
UserStocks was not created with a primary key. Although often used, a pri-
mary key is not required for all tables. As you might expect, a new index can be
created on an existing table and is accomplished by using the CREATE INDEX
keywords. The syntax for creating an index is similar to the syntax used for cre-
ating a table. Adding the UNIQUE keyword means that only unique values can be
used for the compound key. For the previous tables, the indexes were created at
the same time as their respective tables. Because this table already exists, the ON
keyword is used to associate the new index to the proper table name, with the
field names following within parentheses. The WITH keyword declares additional
options for the index. PRIMARY indicates that the new index also will be the
primary key. DISALLOW NULL prevents null values from being allowed in the
index.
The following step enters code to create a primary key index on the
UserStocks table.