SQL Basics - Java Database Programming Bible - page 58

CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP VARCHAR(10) NOT NULL);

The example of Listing 3-2 illustrates the creation of a foreign key. The column
defined as a foreign key, SIGNIFICANT_OTHER, is used to link separate entries in
the customers table.

Listing 3-2: Creating a table containing a foreign key

CREATE TABLE SIGNIFICANT_OTHERS(CUSTOMER_NUMBER INT NOT
NULL PRIMARY KEY, SIGNIFICANT_OTHER INT,
FOREIGN KEY (SIGNIFICANT_OTHER) REFERENCES CUSTOMERS);

The use of Primary Keys and Foreign Keys to link tables was
discussed in Chapter 1 . Linking tables in JOINS is an
important aspect of the use of SQL to retrieve data. Chapter 9
discusses JOINS in more detail.

Cross-Reference

Altering a table

The ALTER TABLE command is primarily used to add, alter, or drop columns. For
example, to add a column for FAX numbers to the Customers Table, you can use the
following command:

ALTER TABLE CUSTOMERS ADD FAX VARCHAR(20);

To change the column width, use this command:

ALTER TABLE CUSTOMERS ALTER COLUMN FAX VARCHAR(30);

Finally, to drop the column completely, use this command:

ALTER TABLE CUSTOMERS DROP COLUMN FAX;

Dropping a table

You remove a table from the database completely by using the DROP command. To
drop the Customers Table, use the following command:

DROP TABLE CUSTOMERS;

Creating, Altering, and Dropping a View

A view is very similar to a table. Like a table, it has a name that can be used to access
it in other queries. In fact, views are sometimes called temporary tables .