Java Reference
In-Depth Information
Unlike most languages, SQL makes specific provision for empty data fields by
allowing you to set them to NULL. A SQL NULL is defined to be a representation of
missing or inapplicable data that is systematic and distinct from all regular values and
independent of data type. This means you can insert a NULL when the value for a
field is unknown or not applicable without any risk that the NULL will be
misinterpreted as a zero or a space. The NULL or NOT NULL constraint lets you
specify whether a field is required to contain valid data or whether it can be left empty.
Keys fields, for example, can never be NULL.
UNIQUE
The UNIQUE constraint is used to specify that all the values in a given column must
be unique. It is used primarily when defining columns that are to be used as keys.
PRIMARY KEY
The primary key is used by the database-management systems as a unique identifier
for a row. For example, a sales order management system might use the
Customer_ID as the primary key in a table of customer names and addresses. This
Customer_ID is inserted into the Orders Table as a foreign key, linking customer
billing and shipping information to the order.
FOREIGN KEY
The DBMS uses the foreign key to link two tables. For example, when you create a
table of customers, you might, for marketing reasons, wish to create a table of their
spouses or significant others. The SQL command you use to do this is shown in the
second listing under the next section, "
Creating a Table
.
"
Creating a table
Listing 3-1
displays the CREATE TABLE statement used to create the table shown in
Table 3 -3
. The statement defines the table name, followed in parentheses by a series
of column definitions. Column definitions simply list the column or field name,
followed by the data type and the optional constraints. Column definitions are
separated by commas, as shown in the example of
Listing 3-1
.
Listing 3-1: CREATE TABLE Statement
CREATE TABLE CONTACT_INFO
(CONTACT_ID INTEGER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
MI CHAR(1) NULL,
LAST_NAME VARCHAR(30) NOT NULL,
STREET VARCHAR(50) NOT NULL,
