SQL Run

Many DBMSs have interactive, graphical tools that let you create and manage tables and table properties such as column definitions and constraints. This chapter explains how to perform those tasks programmatically by using SQL:

The CREATE TABLE statement creates a new table.
The ALTER TABLE statement modifies the structure of an existing table.
The DROP TABLE statement destroys a table and all its data.
The CREATE TEMPORARY TABLE statement creates a table that the DBMS destroys automatically when it’s no longer in use.
The CREATE TABLE AS statement creates a new table from an existing one.

These statements don’t return a result, but your DBMS might print a message indicating whether the statement ran successfully. To see the actual effect the statement had on a table, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.

These statements modify database objects and data, so your database administrator might need to grant you permission to run them.

Creating Tables

Database designers spend considerable time normalizing tables and defining relationships and constraints before they write a line of SQL code. If you’re going to create tables for production databases, then study database design and relational-model principles beyond those presented in Chapter 2.

Recall from “Tables, Columns, and Rows” in Chapter 2 that a database is organized around tables. To a user or an SQL programmer, a database appears to be a collection of one or more tables (and nothing but tables). To create a table, you specify the following:

Table name
Column names
Data types of the columns
Default values of columns
Constraints

The table name and the column names must conform to the rules for SQL identifiers; see “Identifiers” in Chapter 3. The data type of each column is a character, numeric, datetime, or other data type; see “Data Types” in Chapter 3. A default is the value the column takes if you don’t specify a value explicitly. Constraints define properties such as nullability, keys, and permissible values.

You create a new table by using the CREATE TABLE statement, whose general syntax is:

CREATE TABLE table ( column1 data_type1 [col_constraints1], column2 data_type2 [col_constraints2], ... columnN data_typeN [col_constraintsN] [, table_constraint1] [, table_constraint2] ... [, table_constraintM] );

Each column definition has a column name, a data type, and an optional list of one or more column constraints. An optional list of table constraints follows the final column definition. By convention, I start each column definition and table constraint on its own line.

Understanding Constraints

Constraints let you define rules for values allowed in columns (Table 11.1). Your DBMS uses these rules to enforce the integrity of information in the database automatically.

Table 11.1Constraints
Constraint	Description
NOT NULL	Prevents nulls from being inserted into a column
PRIMARY KEY	Sets the table’s primary-key column(s)
FOREIGN KEY	Sets the table’s foreign-key column(s)
UNIQUE	Prevents duplicate values from being inserted into a column
CHECK	Limits the values that can be inserted into a column by using logical (boolean) expressions

Constraints come in two flavors:

A column constraint is part of a column definition and imposes a condition on that column only.
A table constraint is declared independently of a column definition and can impose a condition on more than one column in a table. You must use a table constraint to include more than one column in a single constraint.

You can specify some constraints as either column or table constraints, depending on the context in which they’re used. If a primary key contains one column, for example, then you can define it as either a column constraint or a table constraint. If the primary key has two or more columns, then you must use a table constraint.

Assigning names to constraints lets you manage them efficiently; you can change or delete a named constraint by using the ALTER TABLE statement, for example. Constraint names are optional, but many SQL programmers and database designers name all constraints. It’s not uncommon to leave a NOT NULL constraint unnamed, but you always should name other types of constraints (even if I don’t do so in some of the examples).

If you don’t name a constraint explicitly, then your DBMS will generate a name and assign it to the constraint quietly and automatically. System-assigned names often contain strings of random characters and are cumbersome to use, so use the CONSTRAINT clause to assign your own name instead. Constraint names also appear in warnings, error messages, and logs, which is another good reason to name constraints yourself.

To name a constraint:

Preceding a constraint definition, type:

CONSTRAINT constraint_name

constraint_name is the name of the constraint and is a valid SQL identifier. Constraints names must be unique within a table.

Creating a New Table with CREATE TABLE

This section describes how to create a new table by using a minimal CREATE TABLE statement. Subsequent sections show you how to add column and table constraints to CREATE TABLE.

To create a new table:

Type:

CREATE TABLE table ( column1 data_type1, column2 data_type2, ... columnN data_typeN );

table is the name of the new table to create.

column1, column2,..., columnN are the names of the columns in table. You must create at least one column.

data_type1, data_type2,..., data_typeN specify the SQL data type of each corresponding column. A data type can specify a length, scale, or precision, where applicable; see “Data Types” and subsequent sections in Chapter 3.

The table name must be unique within the database, and each column name must be unique within the table.

Listing 11.1 creates the sample-database table titles.

Listing 11.1Create the sample-database table titles.

CREATE TABLE titles ( title_id CHAR(3) , title_name VARCHAR(40) , type VARCHAR(10) , pub_id CHAR(3) , pages INTEGER , price DECIMAL(5,2), sales INTEGER , pubdate DATE , contract SMALLINT );

Listing 11.2 creates the sample-database table title_authors.

Listing 11.2Create the sample-database table title_authors.

CREATE TABLE title_authors ( title_id CHAR(3) , au_id CHAR(3) , au_order SMALLINT , royalty_share DECIMAL(5,2) );

Tips for CREATE TABLE

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
Your DBMS will generate an error if you try to create a table with a name that already exists in the database. To prevent you from overwriting a table accidentally, SQL requires that you destroy a table explicitly with DROP TABLE before creating a new table with the same name; see “Dropping a Table with DROP TABLE” later in this chapter.
A newly created table is empty (has zero rows). To populate the table with data, use the INSERT statement; see “Inserting Rows with INSERT” in Chapter 10.
Columns allow nulls by default. If you don’t want to allow nulls, then see “Forbidding Nulls with NOT NULL” later in this chapter.
To modify the structure of an existing table, see “Altering a Table with ALTER TABLE” later in this chapter.
To create a table by using the structure and data of an existing table, see “Creating a New Table from an Existing One with CREATE TABLE AS” later in this chapter.
Microsoft SQL Server doesn’t support the data type DATE. To run Listing 11.1, change the data type of the column pubdate to DATETIME.

MySQL might change the type of a CHAR or VARCHAR column silently when creating a table. VARCHAR columns with a length less than four are changed to CHAR, for example.

Forbidding Nulls with NOT NULL

A column’s nullability determines whether its rows can contain nulls—that is, whether values are required or optional in the column. I described nulls and their effects in “Nulls” in Chapter 3, but I’ll review the basics here:

A null is not a value but a marker that means no value has been entered.
A null represents a missing, unknown, or inapplicable value. A null in the column price doesn’t mean that an item has no price or that its price is zero; it means that the price is unknown or has not been set.
A null isn’t the same as zero (0), a blank, or an empty string ('').
Nulls belong to no data type and can be inserted into any column that allows nulls.
In SQL statements, the keyword NULL represents a null.

When you’re defining a nullability constraint, some important considerations are:

A nullability constraint always is a column constraint and not a table constraint; see “Understanding Constraints” earlier in this chapter.
You define a nullability constraint by using the keywords NOT NULL in a CREATE TABLE column definition.
In general, avoid allowing nulls because they complicate queries, insertions, and updates.
Forbidding nulls in a column can help maintain data integrity by ensuring that users entering data must enter a value in the column. The DBMS won’t insert or update a row if a non-nullable column contains a null.
Some other constraints, such as a PRIMARY KEY constraint, can’t be used with nullable columns.
Nulls affect referential-integrity checks in foreign keys; see “Specifying a Foreign Key with FOREIGN KEY” later in this chapter.
If you INSERT a row but include no value for a column that allows null values, then your DBMS supplies a null (unless a DEFAULT clause exists); see “Inserting Rows with INSERT” in Chapter 10 and “Specifying a Default Value with DEFAULT” later in this chapter.
A user can enter NULL explicitly in a nullable column, no matter what data type or default value the column has.
If you don’t specify a NOT NULL constraint, then the column accepts nulls by default.

To specify a column’s nullability:

Add the following column constraint to a CREATE TABLE column definition:

[CONSTRAINT constraint_name] NOT NULL

NOT NULL forbids nulls in a column. If the nullability constraint is omitted, then the column accepts nulls. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the column’s nullability constraint; see “Understanding Constraints” earlier in this chapter.

Listing 11.3 creates the sample-database table authors, forbidding nulls in some columns. Missing addresses and telephone numbers are common, so I’ve allowed nulls in those columns.

Notice that I’ve forbidden nulls in both the first-name and last-name columns. If the author’s name has only a single word (like author A06, Kellsey), then I’ll insert the name into au_lname and insert an empty string ('') into au_fname. Or I could have allowed nulls in au_fname and inserted a null into au_fname for one-named authors. Or I could have allowed nulls in both au_fname and au_lname and added a check constraint that required at least one of the two columns to contain a non-null, non-empty string. The database designer makes these types of decisions before creating a table.

Listing 11.3Create the sample-database table authors. Where omitted, the nullability constraint defaults to allow nulls.

CREATE TABLE authors ( au_id CHAR(3) NOT NULL, au_fname VARCHAR(15) NOT NULL, au_lname VARCHAR(15) NOT NULL, phone VARCHAR(12) , address VARCHAR(20) , city VARCHAR(15) , state CHAR(2) , zip CHAR(5) );

Most DBMSs let you specify only the NULL keyword (without the NOT) to allow nulls. Listing 11.4 creates the sample-database table titles.

Listing 11.4Create the sample-database table titles and assign nullability constraints to each column explicitly.

CREATE TABLE titles ( title_id CHAR(3) NOT NULL, title_name VARCHAR(40) NOT NULL, type VARCHAR(10) NULL , pub_id CHAR(3) NOT NULL, pages INTEGER NULL , price DECIMAL(5,2) NULL , sales INTEGER NULL , pubdate DATE NULL , contract SMALLINT NOT NULL );

Tips for NOT NULL

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
When you insert a row into a table, you must provide values explicitly for columns that prohibit nulls (and have no default value). For the table authors created by Listing 11.3, for example, the minimal INSERT statement looks like this:

INSERT INTO authors( au_id, au_fname, au_lname) VALUES( 'A08', 'Michael', 'Polk');

The DBMS assigns nulls automatically to the columns in authors that aren’t listed in the INSERT column list (phone, address, and so on); see “Inserting Rows with INSERT” in Chapter 10.
When INSERTing a null into a row, don’t quote the keyword NULL; your DBMS will interpret it as the character string 'NULL' rather than as a null.
See also “Testing for Nulls with IS NULL” in Chapter 4, and “Checking for Nulls with COALESCE()” and “Comparing Expressions with NULLIF()” in Chapter 5.
Microsoft SQL Server doesn’t support the data type DATE. To run Listing 11.4, change the data type of the column pubdate to DATETIME.

Oracle treats an empty string ('') as a null; see DBMS tip in “Tips for Nulls” in Chapter 3.

Db2 doesn’t accept a stand-alone NULL keyword (without a NOT) in a nullability constraint. To run Listing 11.4, omit each nullability constraint that isn’t NOT NULL.

Db2 and MySQL don’t accept named NOT NULL constraints. Omit the clause CONSTRAINT constraint_name from NOT NULL column definitions.

Nullability constraints for the DBMSs covered in this book are optional (and allow nulls by default), but other DBMSs might behave differently.

For all DBMSs, check the documentation to see how your DBMS handles nullability constraints for columns whose data type generates a unique row identifier automatically; see “Other Data Types” in Chapter 3.

Specifying a Default Value with DEFAULT

A default specifies a value that your DBMS assigns to a column if you omit a value for the column when inserting a row; see “Inserting Rows with INSERT” in Chapter 10. When you’re defining a default value, some important considerations are:

A default applies to a single column.
You define a default by using the keyword DEFAULT in a CREATE TABLE column definition.
A default value can be any expression that evaluates to a constant.
The default must have the same data type or must be implicitly convertible to the same type as its column; see “Converting Data Types with CAST()” in Chapter 5.
The column must be long enough to hold the default value.
If you INSERT a row without specifying a value for a column, then that column’s default is used. If the column definition has no DEFAULT, then the default is null.
If a column has no DEFAULT and is declared NOT NULL, then you should specify the column’s value explicitly when you INSERT a row (see “Inserting Rows with INSERT” in Chapter 10). If you omit an explicit value in this situation, then some DBMSs will refuse to INSERT the row, whereas others will assign a default automatically based on the column’s date type. MySQL, for example, assigns a default value of zero to numeric, non-nullable columns without explicit defaults.

To specify a column’s default value:

Add the following clause to a CREATE TABLE column definition:

DEFAULT expr

expr is an expression that evaluates to a constant, such as a literal, a built-in function, a mathematical expression, or NULL. If no default is specified, then NULL is assumed. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

Listing 11.5 assigns defaults to some of the columns in the sample-database table titles. The columns title_id and pub_id are NOT NULL and have no default values, so you must provide explicit values for them in an INSERT statement. The pages clause DEFAULT NULL is equivalent to omitting the DEFAULT. The pubdate and contract defaults show that the defaults can be expressions more complex than plain literals.

Listing 11.5Set default values for some of the columns in the sample-database table.

CREATE TABLE titles ( title_id CHAR(3) NOT NULL , title_name VARCHAR(40) NOT NULL DEFAULT '' , type VARCHAR(10) DEFAULT 'undefined' , pub_id CHAR(3) NOT NULL , pages INTEGER DEFAULT NULL , price DECIMAL(5,2) NOT NULL DEFAULT 0.00 , sales INTEGER , pubdate DATE DEFAULT CURRENT_DATE, contract SMALLINT NOT NULL DEFAULT (3*7)-21 );

Listing 11.6 shows the minimal INSERT statement that you can use to insert a row into the table titles (as created by Listing 11.5). Figure 11.1 shows the inserted row, with default values highlighted. The title_name default, an empty string (''), is invisible.

Listing 11.6The DBMS inserts default values into columns omitted from this INSERT statement. Where no default is specified, the DBMS inserts a null. See Figure 11.1 for the result.

INSERT INTO titles(title_id, pub_id) VALUES('T14','P01');

Figure 11.1Listing 11.6 inserts this row into the table titles.

title_id title_name type pub_id pages price sales pubdate contract -------- ------------- ---------- ------ ----- ----- ----- ---------- -------- T14 undefined P01 NULL 0.00 NULL 2005-02-21 0

Tips for DEFAULT

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
Microsoft Access doesn’t allow arithmetic expressions in a DEFAULT clause; use a numeric literal. Use Date() instead of CURRENT_DATE to return the system date. (See the DBMS tip in “Tips for the Current Date and Time” in Chapter 5.) To run Listing 11.5, change the default clause of the column pubdate to DEFAULT Date() and the default clause of the column contract to DEFAULT 0.

Microsoft SQL Server doesn’t support the data type DATE; use DATETIME instead. Use GETDATE() instead of CURRENT_DATE to return the system date; see the DBMS tip in “Tips for the Current Date and Time” in Chapter 5. To run Listing 11.5, change the pubdate column’s data type to DATETIME, and change its default clause to DEFAULT GETDATE().

In Oracle, the DEFAULT clause follows the data type and precedes all column constraints, including the nullability constraint. Oracle 9i and later versions support CURRENT_DATE; use SYSDATE instead of CURRENT_DATE in Oracle 8i and earlier; see the DBMS tip in “Tips for the Current Date and Time” in Chapter 5. Oracle treats an empty string ('') as a null, so I’ve changed the title_name default to a space character (' '); see the DBMS tip in “Tips for Nulls” in Chapter 3. See Listing 11.7 for the Oracle version of Listing 11.5.

Db2 doesn’t support arithmetic expressions as default values. To run Listing 11.5, change the default clause of the column contract to DEFAULT 0.

In MySQL, a default value must be a literal and not a function or expression. This restriction means that you can’t set the default of a date column to CURRENT_DATE. To run Listing 11.5, delete the DEFAULT clause of the column pubdate (or change the default expression to a datetime literal), and change the DEFAULT clause of the column contract to DEFAULT 0. (Exception: You can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.)

For all DBMSs, check the documentation to see how your DBMS handles default clauses for columns whose data type generates a unique row identifier automatically; see “Other Data Types” in Chapter 3.

Listing 11.7In Oracle, the default clause must come before all column constraints.

CREATE TABLE titles ( title_id CHAR(3) NOT NULL, title_name VARCHAR(40) DEFAULT ' ' NOT NULL, type VARCHAR(10) DEFAULT 'undefined' , pub_id CHAR(3) NOT NULL, pages INTEGER DEFAULT NULL , price DECIMAL(5,2) DEFAULT 0.00 NOT NULL, sales INTEGER , pubdate DATE DEFAULT SYSDATE , contract SMALLINT DEFAULT (3*7)-21 NOT NULL );

Specifying a Primary Key with PRIMARY KEY

I described primary keys in “Primary Keys” in Chapter 2, but I’ll review the basics here:

A primary key identifies each row uniquely in a table.
No two rows can have the same primary-key value.
Primary keys don’t allow nulls.
Each table has exactly one primary key.
A one-column key is a simple key; a multiple-column key is a composite key.
In a composite key, values can be duplicated within one column, but each combination of values from all the key’s columns must be unique.
A table can have more than one combination of columns that uniquely identify its rows; each combination is a candidate key. The database designer picks one of the candidate keys to be the primary key.

When you’re defining a primary-key constraint, some important considerations are:

A simple key can be a column constraint or a table constraint; a composite key always is a table constraint. See “Understanding Constraints” earlier in this chapter.
You define a primary-key constraint by using the keywords PRIMARY KEY in a CREATE TABLE definition.
As a table constraint, PRIMARY KEY makes you specify column name(s) explicitly. As a column constraint, PRIMARY KEY applies to the column in which it’s defined.
The SQL standard lets you create a table without a primary key (in violation of the relational model). In practice, you always should define a primary key for every table.
No more than one primary-key constraint is allowed in a table.
In practice, primary-key constraints almost always are named explicitly. Use a CONSTRAINT clause to do so; see “Understanding Constraints” earlier in this chapter.
The nullability of all PRIMARY KEY columns must be NOT NULL. If you don’t specify a nullability constraint, then the DBMS sets all primary-key columns to NOT NULL implicitly; see “Forbidding Nulls with NOT NULL” earlier in this chapter.
You must specify a primary-key value explicitly when you INSERT a row unless the column’s data type generates a unique row identifier automatically; see “Other Data Types” in Chapter 3. For information about inserting rows, see “Inserting Rows with INSERT” in Chapter 10.
Primary-key values normally don’t change after they’re inserted.
For considerations related to inserting, updating, and deleting primary keys that are referenced by foreign keys, see “Specifying a Foreign Key with FOREIGN KEY” later in this chapter.
The DBMS will create a unique index for a primary key automatically (see Chapter 12).

To specify a simple primary key:

To specify a simple primary key as a column constraint, add the following column constraint to a CREATE TABLE column definition:

[CONSTRAINT constraint_name] PRIMARY KEY

or

To specify a simple primary key as a table constraint, add the following table constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] PRIMARY KEY (key_column)

key_column is the name of the primary-key column. No more than one PRIMARY KEY constraint is allowed in a table. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the primary-key constraint; see “Understanding Constraints” earlier in this chapter.

Listings 11.8a, 11.8b, and 11.8c show three equivalent ways to define a simple primary key for the sample-database table publishers.

Listing 11.8a uses a column constraint to designate the primary-key column. This syntax shows the easiest way to create a simple primary key.

Listing 11.8aDefine a simple primary key for the sample-database table publishers by using a column constraint.

CREATE TABLE publishers ( pub_id CHAR(3) PRIMARY KEY, pub_name VARCHAR(20) NOT NULL , city VARCHAR(15) NOT NULL , state CHAR(2) , country VARCHAR(15) NOT NULL );

Listing 11.8b uses an unnamed table constraint to specify the primary key. I’ve added an explicit NOT NULL column constraint to pub_id, but it’s unnecessary because the DBMS sets this constraint implicitly and silently (except for Db2; see the DBMS tip in “Tips for PRIMARY KEY” in this section).

Listing 11.8bDefine a simple primary key for the sample-database table publishers by using an unnamed table constraint.

CREATE TABLE publishers ( pub_id CHAR(3) NOT NULL, pub_name VARCHAR(20) NOT NULL, city VARCHAR(15) NOT NULL, state CHAR(2) , country VARCHAR(15) NOT NULL, PRIMARY KEY (pub_id) );

Listing 11.8c uses a named table constraint to specify the primary key. This syntax shows the preferred way to add a primary key; you can use the name publishers_pk if you decide to change or delete the key later. See “Altering a Table with ALTER TABLE” later in this chapter.

Listing 11.8cDefine a simple primary key for the sample-database table publishers by using a named table constraint.

CREATE TABLE publishers ( pub_id CHAR(3) NOT NULL, pub_name VARCHAR(20) NOT NULL, city VARCHAR(15) NOT NULL, state CHAR(2) , country VARCHAR(15) NOT NULL, CONSTRAINT publishers_pk PRIMARY KEY (pub_id) );

To specify a composite primary key:

Add the following table constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] PRIMARY KEY (key_columns)

key_columns is a list of comma-separated names of the primary-key columns. No more than one PRIMARY KEY constraint is allowed in a table. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the primary-key constraint; see “Understanding Constraints” earlier in this chapter.

Listing 11.9 defines a composite primary key for the sample-database table title_authors. The primary-key columns are title_id and au_id, and the key is named title_authors_pk.

Listing 11.9Define a composite primary key for the sample-database table title_authors by using a named table constraint.

CREATE TABLE title_authors ( title_id CHAR(3) NOT NULL, au_id CHAR(3) NOT NULL, au_order SMALLINT NOT NULL, royalty_share DECIMAL(5,2) NOT NULL, CONSTRAINT title_authors_pk PRIMARY KEY (title_id, au_id) );

Tips for PRIMARY KEY

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
To define a column that contains unique values but isn’t a primary key, see “Forcing Unique Values with UNIQUE” later in this chapter.
It’s illegal to specify two or more PRIMARY KEY column constraints in the same table. You can’t use the following statement, for example, to specify the composite key for title_authors:

CREATE TABLE title_authors( title_id CHAR(3) PRIMARY KEY, au_id CHAR(3) PRIMARY KEY, au_order SMALLINT NOT NULL, ... ); --Illegal
Db2 makes you set the nullability constraint to NOT NULL explicitly for PRIMARY KEY columns; see “Forbidding Nulls with NOT NULL” earlier in this chapter. To run Listing 11.8a, add NOT NULL to pub_id’s column constraint.

Oracle treats an empty string ('') as a null; see the DBMS tip in “Tips for Nulls” in Chapter 3.

Specifying a Foreign Key with FOREIGN KEY

I described foreign keys in “Foreign Keys” in Chapter 2, but I’ll review the basics here:

A foreign key is a mechanism that associates two tables.
A foreign key is a column (or set of columns) in a table whose values relate to, or reference, values in some other table.
A foreign key ensures that rows in one table have corresponding rows in another table, called the referenced table or parent table.
A foreign key establishes a direct relationship to a primary key or candidate key in the referenced table, so foreign-key values are restricted to parent-key values that already exist. This restriction is called referential integrity.
A foreign key, unlike a primary key, can allow nulls.
A table can have zero or more foreign keys.
Foreign-key values generally aren’t unique in their own table.
Foreign-key columns in different tables can reference the same column in a parent table.
A one-column key is a simple key; a multiple-column key is a composite key.

When you’re defining a foreign-key constraint, some important considerations are:

A simple key can be a column constraint or a table constraint; a composite key always is a table constraint. See “Understanding Constraints” earlier in this chapter.
You define a foreign-key constraint by using the keywords FOREIGN KEY or REFERENCES in a CREATE TABLE definition.
A foreign key and its parent key can have different column names.
The foreign key’s data type must have the same data type or must be convertible implicitly to the same type as its parent key; see “Converting Data Types with CAST()” in Chapter 5.
A FOREIGN KEY column doesn’t have to reference only a PRIMARY KEY column in another table; it also can reference a UNIQUE column in another table. See “Forcing Unique Values with UNIQUE” later in this chapter.
A table can have any number of foreign-key constraints (or none at all).
In practice, foreign-key constraints almost always are named explicitly. Use a CONSTRAINT clause to name a constraint; see “Understanding Constraints” earlier in this chapter.
Foreign-key constraints simplify updates and deletions and make it difficult to introduce inconsistencies into a database, but the topology of relations in even a medium-size database can become astonishingly complex. Poor design can lead to time-consuming routine queries, circular rules, tricky backup-and-restore operations, and psychotically ambitious cascading deletes.

To preserve referential integrity, your DBMS won’t let you create orphan rows or make existing rows orphans (rows in a foreign-key table without an associated row in a parent table). When you INSERT, UPDATE, or DELETE a row with a FOREIGN KEY column that references a PRIMARY KEY column in a parent table, your DBMS performs the following referential-integrity checks:

Inserting a row into the foreign-key table.The DBMS checks that the new FOREIGN KEY value matches a PRIMARY KEY value in the parent table. If no match exists, then the DBMS won’t INSERT the row.

Updating a row in the foreign-key table.The DBMS checks that the updated FOREIGN KEY value matches a PRIMARY KEY value in the parent table. If no match exists, then the DBMS won’t UPDATE the row.

Deleting a row in the foreign-key table.A referential-integrity check is unnecessary.

Inserting a row into the parent table.A referential-integrity check is unnecessary.

Updating a row in the parent table.The DBMS checks that none of the FOREIGN KEY values matches the PRIMARY KEY value to be updated. If a match exists, then the DBMS won’t UPDATE the row.

Deleting a row from the parent table.The DBMS checks that none of the FOREIGN KEY values matches the PRIMARY KEY value to be deleted. If a match exists, then the DBMS won’t DELETE the row.

The DBMS skips the referential-integrity check for rows with a null in the FOREIGN KEY column.

To specify a simple foreign key:

To specify a simple foreign key as a column constraint, add the following column constraint to a CREATE TABLE column definition:

[CONSTRAINT constraint_name] REFERENCES ref_table(ref_column)

or

To specify a simple foreign key as a table constraint, add the following table constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] FOREIGN KEY (key_column) REFERENCES ref_table(ref_column)

key_column is the name of the foreign-key column. ref_table is the name of the parent table referenced by the FOREIGN KEY constraint. ref_column is the name of the column in ref_table that is the referenced key. Zero or more FOREIGN KEY constraints are allowed in a table. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the foreign-key constraint; see “Understanding Constraints” earlier in this chapter.

Listing 11.10 uses a column constraint to designate a foreign-key column in the table titles. This syntax shows the easiest way to create a simple foreign key. After you run this statement, the DBMS will ensure that values inserted into the column pub_id in titles already exist in the column pub_id in publishers. Note that nulls aren’t allowed in the foreign-key column, so every book must have a publisher.

Listing 11.10Define a simple foreign key for the sample-database table titles by using a column constraint.

CREATE TABLE titles ( title_id CHAR(3) NOT NULL PRIMARY KEY , title_name VARCHAR(40) NOT NULL, type VARCHAR(10) , pub_id CHAR(3) NOT NULL REFERENCES publishers(pub_id) , pages INTEGER , price DECIMAL(5,2) , sales INTEGER , pubdate DATE , contract SMALLINT NOT NULL );

The table royalties has a one-to-one relationship with the table titles, so Listing 11.11 defines the column title_id to be both the primary key and a foreign key that points to title_id in titles. For information about relationships, see “Relationships” in Chapter 2.

Listing 11.11Define a simple foreign key for the sample-database table royalties by using a named table constraint.

CREATE TABLE royalties ( title_id CHAR(3) NOT NULL, advance DECIMAL(9,2) , royalty_rate DECIMAL(5,2) , CONSTRAINT royalties_pk PRIMARY KEY (title_id), CONSTRAINT royalties_title_id_fk FOREIGN KEY (title_id) REFERENCES titles(title_id) );

Listing 11.12 uses named table constraints to create two foreign keys. This syntax shows the preferred way to add foreign keys; you can use the names if you decide to change or delete the keys later. (See “Altering a Table with ALTER TABLE” later in this chapter.) Each foreign-key column is an individual key and not part of a single composite key. Note that foreign keys together, however, comprise the table’s composite primary key.

Listing 11.12Define simple foreign keys for the sample-database table title_authors by using named table constraints.

CREATE TABLE title_authors ( title_id CHAR(3) NOT NULL, au_id CHAR(3) NOT NULL, au_order SMALLINT NOT NULL, royalty_share DECIMAL(5,2) NOT NULL, CONSTRAINT title_authors_pk PRIMARY KEY (title_id, au_id), CONSTRAINT title_authors_fk1 FOREIGN KEY (title_id) REFERENCES titles(title_id), CONSTRAINT title_authors_fk2 FOREIGN KEY (au_id) REFERENCES authors(au_id) );

To specify a composite foreign key:

Add the following table constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] FOREIGN KEY (key_columns) REFERENCES ref_table(ref_columns)

key_columns is a list of comma-separated names of the foreign-key columns. ref_table is the name of the parent table referenced by the FOREIGN KEY constraint. ref_columns is a list of comma-separated names of the columns in ref_table that are the referenced keys. key_columns and ref_columns must have the same number of columns, listed in corresponding order. Zero or more FOREIGN KEY constraints are allowed in a table. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the foreign-key constraint; see “Understanding Constraints” earlier in this chapter.

The sample database contains no composite foreign keys, but suppose that I create a table named out_of_print to store information about each author’s out-of-print books. The table title_authors has a composite primary key. This constraint shows how to reference this key from the table out_of_print:

CONSTRAINT out_of_print_fk FOREIGN KEY (title_id, au_id) REFERENCES title_authors(title_id, au_id)

Tips for FOREIGN KEY

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
You can omit the (ref_column) or (ref_columns) expression in the REFERENCES clause if the referenced column(s) is the primary key of ref_table.
A FOREIGN KEY constraint can reference another column in the same table (a self-reference). Recall from “Creating a Self-Join” in Chapter 7 that the table employees is self-referencing.

employees has three columns: emp_id, emp_name, and boss_id. emp_id is a primary key that uniquely identifies an employee, and boss_id is an employee ID that identifies the employee’s manager. Each manager also is an employee, so to ensure that each manager ID that is added to the table matches an existing employee ID, boss_id is defined as a foreign key of emp_id:

CREATE TABLE employees ( emp_id CHAR(3) NOT NULL, emp_name CHAR(20) NOT NULL, boss_id CHAR(3) NULL, CONSTRAINT employees_pk PRIMARY KEY (emp_id), CONSTRAINT employees_fk FOREIGN KEY (boss_id) REFERENCES employees(emp_id) );
SQL lets you define the action the DBMS takes when you try to UPDATE or DELETE a key value (in a parent table) to which foreign-key values point. To trigger a referential action, specify an ON UPDATE or ON DELETE clause in the FOREIGN KEY constraint. Support for these clauses varies by DBMS; search your DBMS documentation for foreign key or referential integrity. The next two tips explain the SQL standard’s definition of these clauses.
The ON UPDATE action clause specifies what the DBMS does if you attempt to UPDATE a key value in a row (in a parent table) where the key value is referenced by foreign keys in rows in other tables. action takes one of four values:

CASCADE updates the dependent foreign-key values to the new parent-key value.

SET NULL sets the dependent foreign-key values to nulls.

SET DEFAULT sets the dependent foreign-key values to their default values; see “Specifying a Default Value with DEFAULT” earlier in this chapter.

NO ACTION generates an error on a foreign-key violation. This action is the default.
The ON DELETE action clause specifies what the DBMS does if you attempt to DELETE a key value in a row (in a parent table) where the key value is referenced by foreign keys in rows in other tables. action takes one of four values:

CASCADE deletes the rows that contain foreign-key values that match the deleted parent-key value.

SET NULL sets the dependent foreign-key values to null.

SET DEFAULT sets the dependent foreign-key values to their default values; see “Specifying a Default Value with DEFAULT” earlier in this chapter.

NO ACTION generates an error on a foreign-key violation. This action is the default.
Microsoft SQL Server doesn’t support the data type DATE. To run Listing 11.10, change the data type of the column pubdate to DATETIME.

Oracle treats an empty string ('') as a null; see the DBMS tip in “Tips for Nulls” in Chapter 3.

MySQL enforces foreign-key constraints through InnoDB tables; search MySQL documentation for foreign key. InnoDB FOREIGN KEY syntax is more restrictive than standard CREATE TABLE syntax.

Forcing Unique Values with UNIQUE

A unique constraint ensures that a column (or set of columns) contains no duplicate values. A unique constraint is similar to a primary-key constraint, except that a unique column can contain nulls and a table can have multiple unique columns. (For information about primary-key constraints, see “Specifying a Primary Key with PRIMARY KEY” earlier in this chapter.)

Suppose that I add the column isbn to the table titles to hold a book’s ISBN. An ISBN is a unique, standardized identification number that marks a book unmistakably. titles already has a primary key (title_id), so to ensure that each ISBN value is unique, I can define a unique constraint on the column isbn.

When you’re defining a unique constraint, some important considerations are:

A one-column key is a simple constraint; a multiple-column key is a composite constraint.
In a composite constraint, values can be duplicated within one column, but each combination of values from all the columns must be unique.
A simple unique constraint can be a column constraint or a table constraint; a composite unique constraint always is a table constraint. See “Understanding Constraints” earlier in this chapter.
You define a unique constraint by using the keyword UNIQUE in a CREATE TABLE definition.
As a table constraint, UNIQUE makes you specify column name(s). As a column constraint, UNIQUE applies to the column in which it’s defined.
A table can have zero or more unique constraints.
In practice, unique constraints almost always are named explicitly. Use a CONSTRAINT clause to name a constraint; see “Understanding Constraints” earlier in this chapter.
A UNIQUE column can forbid nulls; see “Forbidding Nulls with NOT NULL” earlier in this chapter.

To specify a simple unique constraint:

To specify a simple unique constraint as a column constraint, add the following column constraint to a CREATE TABLE column definition:

[CONSTRAINT constraint_name] UNIQUE

or

To specify a simple unique constraint as a table constraint, add the following table constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] UNIQUE (unique_column)

unique_column is the name of the column that forbids duplicate values. Zero or more UNIQUE constraints are allowed in a table. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the unique constraint; see “Understanding Constraints” earlier in this chapter.

Listings 11.13a and 11.13b show two equivalent ways to define a simple unique constraint for the sample-database table titles.

Listing 11.13a uses a column constraint to designate a unique column. This syntax shows the easiest way to create a simple unique constraint.

Listing 11.13aDefine a simple unique constraint on the column title_name for the sample-database table titles by using a column constraint.

CREATE TABLE titles ( title_id CHAR(3) PRIMARY KEY , title_name VARCHAR(40) NOT NULL UNIQUE, type VARCHAR(10) , pub_id CHAR(3) NOT NULL , pages INTEGER , price DECIMAL(5,2) , sales INTEGER , pubdate DATE , contract SMALLINT NOT NULL );

Listing 11.13b uses a named table constraint to specify a unique column. This syntax shows the preferred way to add a unique constraint; you can use the name if you decide to change or delete the constraint later. See “Altering a Table with ALTER TABLE” later in this chapter.

Listing 11.13bDefine a simple unique constraint on the column title_name for the sample-database table titles by using a named table constraint.

CREATE TABLE titles ( title_id CHAR(3) NOT NULL, title_name VARCHAR(40) NOT NULL, type VARCHAR(10) , pub_id CHAR(3) NOT NULL, pages INTEGER , price DECIMAL(5,2) , sales INTEGER , pubdate DATE , contract SMALLINT NOT NULL, CONSTRAINT titles_pk PRIMARY KEY (title_id), CONSTRAINT titles_unique1 UNIQUE (title_name) );

To specify a composite unique constraint:

Add the following table constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] UNIQUE (unique_columns)

unique_columns is a list of comma-separated names of the columns that forbid duplicate values. Zero or more unique constraints are allowed in a table. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the unique constraint; see “Understanding Constraints” earlier in this chapter.

Listing 11.14 defines a multicolumn unique constraint for the sample-database table authors. This constraint forces the combination of each author’s first and last name to be unique.

Listing 11.14Define a composite unique constraint on the columns au_fname and au_lname for the sample-database table authors by using a named table constraint.

CREATE TABLE authors ( au_id CHAR(3) NOT NULL, au_fname VARCHAR(15) NOT NULL, au_lname VARCHAR(15) NOT NULL, phone VARCHAR(12) , address VARCHAR(20) , city VARCHAR(15) , state CHAR(2) , zip CHAR(5) , CONSTRAINT authors_pk PRIMARY KEY (au_id), CONSTRAINT authors_unique1 UNIQUE (au_fname, au_lname) );

Tips for UNIQUE

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
A foreign-key column can point to a UNIQUE column; see “Specifying a Foreign Key with FOREIGN KEY” earlier in this chapter.
You should assign non-nullable unique constraints to alternate keys; see “Primary Keys” in Chapter 2.
You can create a unique index instead of a unique constraint; see “Creating an Index with CREATE INDEX” in Chapter 12. To determine whether your DBMS prefers an index or a constraint, search your DBMS documentation for unique, index, or constraint.
Microsoft SQL Server doesn’t support the data type DATE. To run Listings 11.13a and 11.13b, change the data type of the column pubdate to DATETIME.

Oracle treats an empty string ('') as a null; see the DBMS tip in “Tips for Nulls” in Chapter 3.

Db2 makes you set the nullability constraint to NOT NULL explicitly for PRIMARY KEY columns. To run Listing 11.13a, add NOT NULL to title_id’s column constraint.

The SQL standard allows any number of nulls in a nullable, unique column. Microsoft SQL Server allows only one null in such a column, and Db2 allows none.

Adding a Check Constraint with CHECK

So far, the only restrictions on an inserted value are that it have the proper data type, size, and range for its column. You can use check constraints to further limit the values that a column (or set of columns) accepts. Check constraints commonly are used to check the following:

Minimum or maximum values.Prevent sales of fewer than zero items, for example.

Specific values.Allow only 'biology', 'chemistry', or 'physics' in the column science, for example.

A range of values.Make sure that an author’s royalty rate is between 2 percent and 20 percent, for example.

A check constraint resembles a foreign-key constraint in that both restrict the values that can be placed in a column (see “Specifying a Foreign Key with FOREIGN KEY” earlier in this chapter). They differ in how they determine which values are allowed. A foreign-key constraint gets the list of valid values from another table, whereas a check constraint determines the valid values by using a logical (boolean) expression. The following check constraint, for example, ensures that no employee’s salary exceeds $50000:

CHECK (salary <= 50000)

When you’re defining a check constraint, some important considerations are:

A check constraint that applies to a single column can be a column constraint or a table constraint; a check constraint that applies to multiple columns always is a table constraint. See “Understanding Constraints” earlier in this chapter.
You define a check constraint by using the keyword CHECK in a CREATE TABLE definition.
A column can have zero or more check constraints associated with it.
If you create multiple check constraints for a column, then design them carefully so that their purposes don’t conflict. Don’t assume that the DBMS will evaluate the constraints in any particular order or will verify that the constraints are mutually exclusive.
In practice, check constraints almost always are named explicitly. Use a CONSTRAINT clause to name a constraint; see “Understanding Constraints” earlier in this chapter.
The check constraint’s condition is almost any valid WHERE condition, such as a comparison (=, <>, <, <=, >, >=), LIKE, BETWEEN, IN, or IS NULL condition. (Most DBMSs don’t allow subqueries in check constraints.) You can join multiple conditions with AND, OR, or NOT. For information about conditions, see “Filtering Rows with WHERE” and subsequent sections in Chapter 4.
A check constraint’s condition can refer to any column in the table, but it can’t refer to columns in other tables.
Although it’s possible to add check constraints after a table has been populated, it’s a better practice to impose check constraints before populating the table to detect input errors as early as possible.

To add a check constraint:

To add a check constraint as a column constraint or table constraint, add the following constraint to a CREATE TABLE definition:

[CONSTRAINT constraint_name] CHECK (condition)

condition is a logical (boolean) condition that the DBMS evaluates each time an INSERT, UPDATE, or DELETE statement modifies the contents of the table. If condition evaluates to true or unknown (due to a null) after the modification, then the DBMS allows the change. If condition evaluates to false, then the DBMS undoes the change and returns an error. For the general syntax of CREATE TABLE, see “Creating Tables” earlier in this chapter.

The CONSTRAINT clause is optional, and constraint_name is the name of the primary-key constraint; see “Understanding Constraints” earlier in this chapter.

Listing 11.15 shows various column and table check constraints for the sample-database table titles. The constraint title_id_chk makes sure the each primary-key value takes the form 'Tnn', in which nn represents an integer between 00 and 99, inclusive.

Listing 11.15Define some check constraints for the sample-database table titles.

CREATE TABLE titles ( title_id CHAR(3) NOT NULL, title_name VARCHAR(40) NOT NULL, type VARCHAR(10) CONSTRAINT type_chk CHECK (type IN ('biography', 'children', 'computer', 'history', 'psychology')) , pub_id CHAR(3) NOT NULL, pages INTEGER CHECK (pages > 0) , price DECIMAL(5,2) , sales INTEGER , pubdate DATE , contract SMALLINT NOT NULL, CONSTRAINT titles_pk PRIMARY KEY (title_id), CONSTRAINT titles_pub_id_fk FOREIGN KEY (pub_id) REFERENCES publishers(pub_id), CONSTRAINT title_id_chk CHECK ( (SUBSTRING(title_id FROM 1 FOR 1) = 'T') AND (CAST(SUBSTRING(title_id FROM 2 FOR 2) AS INTEGER) BETWEEN 0 AND 99)), CONSTRAINT price_chk CHECK (price >= 0.00 AND price < 100.00), CONSTRAINT sales_chk CHECK (sales >= 0), CONSTRAINT pubdate_chk CHECK (pubdate >= DATE '1950-01-01'), CONSTRAINT title_name_chk CHECK (title_name <> '' AND contract >= 0), CONSTRAINT revenue_chk CHECK (price * sales >= 0.00) );

Tips for CHECK

To see the result of a CREATE TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
The SQL standard says that a check condition can’t reference the datetime and user functions (CURRENT_TIMESTAMP, CURRENT_USER, and so on), but some DBMSs, such as Microsoft Access, Microsoft SQL Server, and PostgreSQL, allow them, for example:

CHECK(ship_time >= CURRENT_TIMESTAMP)

These functions are described in “Getting the Current Date and Time” and “Getting User Information” in Chapter 5.
To run Listing 11.15 in Microsoft Access, convert the two column constraints (for the columns type and pages) to table constraints by moving them after the last column definition. Change the first substring expression to Mid(title_id, 1, 1); change the CAST expression to CInt(Mid(title_id, 2, 2)); and drop the keyword DATE from the date literal and surround it with # characters (#1950-01-01#) instead of quotes.

To run Listing 11.15 in Microsoft SQL Server, change the data type of the column pubdate to DATETIME; change the two substring expressions to SUBSTRING(title_id, 1, 1) and SUBSTRING(title_id, 2, 2); and drop the keyword DATE from the date literal ('1950-01-01').

To run Listing 11.15 in Oracle, change the two substring expressions to SUBSTR(title_id, 1, 1) and SUBSTR(title_id, 2, 2).

To run Listing 11.15 in Db2, change the two substring expressions to SUBSTR(title_id, 1, 1) and SUBSTR(title_id, 2, 2) and drop the keyword DATE from the date literal ('1950-01-01').

MySQL 8.0 and later support named, enforced CHECK column constraints, whereas earlier versions do not. To run Listing 11.15 in earlier MySQL versions, remove CONSTRAINT type_chk. Also, in all versions, change the CAST data type from INTEGER to SIGNED.

To run Listing 11.15 in older PostgreSQL versions, change the floating-point literals 0.00 and 100.00 to CAST(0.00 AS DECIMAL) and CAST(100.00 AS DECIMAL); see “Converting Data Types with CAST()” in Chapter 5.

In Microsoft SQL Server, you can specify the check constraint title_id_chk alternatively as CHECK (title_id LIKE '[T][0-9][0-9]'); search SQL Server Help for pattern or wildcard.

Oracle treats an empty string ('') as a null; see the DBMS tip in “Tips for Nulls” in Chapter 3.

Creating a Temporary Table with CREATE TEMPORARY TABLE

Every table I’ve created so far has been a permanent table, called a base table, which stores data persistently until you destroy (DROP) the table explicitly. SQL also lets you create temporary tables to use for working storage or intermediate results. Temporary tables commonly are used to:

Store the result of a complex, time-consuming query once and use the result repeatedly in subsequent queries, improving performance greatly.
Create an image, or snapshot, of a table at a particular moment in time. (You can add a column with the DEFAULT value CURRENT_TIMESTAMP to record the time.)
Hold the result of a subquery.
Hold intermediate results of long or complex calculations.

A temporary table is a table that the DBMS empties automatically at the end of a session or transaction. (The table’s data are destroyed along with the table.) A session is the time during which you’re connected to a DBMS—between logon and logoff—and the DBMS accepts and executes your commands.

When you’re creating a temporary table, some important considerations are:

Temporary tables follow the same rules as base tables with regard to table names, column names, date types, and so on.
You define a temporary table by using a standard CREATE TABLE statement with a bit of extra syntax. Add the keywords GLOBAL TEMPORARY or LOCAL TEMPORARY before the keyword TABLE.
A temporary table has no rows initially. You can INSERT, UPDATE, and DELETE rows as you would in a base table (see Chapter 10).
If you create a large temporary table, then you can free memory by destroying it yourself rather than waiting for the DBMS to do so; see “Dropping a Table with DROP TABLE” later in this chapter.
CREATE TEMPORARY TABLE lets database administrators give users working storage space without giving them (potentially disastrous) CREATE TABLE, ALTER TABLE, or DROP TABLE privileges.

To create a temporary table:

Type:

CREATE {LOCAL | GLOBAL} TEMPORARY TABLE table ( column1 data_type1 [constraints1], column2 data_type2 [constraints2], ... columnN data_typeN [constraintsN] [, table_constraints] );

table is the name of the temporary table to create. LOCAL specifies that table is a local temporary table. GLOBAL specifies that table is a global temporary table (Listings 11.16 and 11.17).

column1, column2,..., columnN are the names of the columns in table. data_type1, data_type2,..., data_typeN specify the SQL data type of each corresponding column.

The permissible column constraints and table constraints for temporary tables vary by DBMS; search your DBMS documentation for temporary tables. For general information about constraints, see “Understanding Constraints” earlier in this chapter.

Listing 11.16A local temporary table is available to only you. It dematerializes when your DBMS session ends.

CREATE LOCAL TEMPORARY TABLE editors ( ed_id CHAR(3) , ed_fname VARCHAR(15), ed_lname VARCHAR(15), phone VARCHAR(12), pub_id CHAR(3) );

Listing 11.17A global temporary table can be accessed by you and other users. It dematerializes when your DBMS session ends and all other tasks have stopped referencing it.

CREATE GLOBAL TEMPORARY TABLE editors ( ed_id CHAR(3) , ed_fname VARCHAR(15), ed_lname VARCHAR(15), phone VARCHAR(12), pub_id CHAR(3) );

Tips for CREATE TEMPORARY TABLE

To see the result of a CREATE TEMPORARY TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
To modify a temporary table, see “Altering a Table with ALTER TABLE” later in this chapter.
To create a temporary copy of an existing table, see “Creating a New Table from an Existing One with CREATE TABLE AS” later in this chapter.
Microsoft Access doesn’t support temporary tables.

The Microsoft SQL Server syntax to create a temporary table is, for a local table:

CREATE TABLE #table (...);

or, for a global table:

CREATE TABLE ##table (...);

You must include the # character(s) whenever you refer to a temporary table by name.

The Oracle syntax to create a temporary table is:

CREATE GLOBAL TEMPORARY TABLE table (...);

The Db2 syntax to create a temporary table is:

DECLARE GLOBAL TEMPORARY TABLE table (...);

MySQL doesn’t distinguish between local and global temporary tables; omit the keyword LOCAL or GLOBAL.

PostgreSQL supports (but ignores) the GLOBAL and LOCAL keywords and creates only one type of temporary table.

Your DBMS might support the optional ON COMMIT clause that is defined by the SQL standard. ON COMMIT PRESERVE ROWS preserves any data modifications to the temporary table on a COMMIT, whereas ON COMMIT DELETE ROWS empties the table after a COMMIT. For information about COMMIT, see Chapter 14.

For all DBMSs, check the documentation to see how the DBMS handles a temporary table that has the same name as a base table. In some cases, for example, a temporary table will hide, or occlude, the like-named base table until the temporary table is dropped.

As you can see, the SQL standard’s definition of the behavior of temporary tables is widely ignored. DBMSs vary in how they implement temporary tables with respect to their persistence, visibility, constraints, foreign keys (referential integrity), indexes, and views; search your DBMS documentation for temporary tables.

Creating a New Table from an Existing One with CREATE TABLE AS

The CREATE TABLE AS statement creates a new table and populates it with the result of a SELECT. It’s similar to creating an empty table with CREATE TABLE and then populating the table with INSERT SELECT (see “Inserting Rows with INSERT” in Chapter 10). CREATE TABLE AS commonly is used to:

Archive specific rows
Make backup copies of tables
Create a snapshot of a table at a particular moment in time
Quickly duplicate a table’s structure but not its data
Create test data
Copy a table to test INSERT, UPDATE, and DELETE operations before modifying production data

When you’re using CREATE TABLE AS, some important considerations are:

You can choose rows for the new table by using the standard SELECT clauses WHERE, JOIN, GROUP BY, and HAVING or any of the SELECT options described in Chapters 4 through 9.
CREATE TABLE AS inserts rows into a single table regardless of how many source tables the SELECT references.
The properties of the columns and expressions in the SELECT-clause list define the new table’s structure.
When you include a derived (computed) column in the SELECT-clause list, the values in the new table’s corresponding column are the values that were computed at the time CREATE TABLE AS was executed. See “Creating Derived Columns” in Chapter 5.
The new table must have a different name from the existing table.
You must have CREATE TABLE permission from your database administrator.

To create a new table from an existing table:

Type:

CREATE TABLE new_table AS subquery;

new_table is the name of the table to create. subquery is a SELECT statement that returns rows to insert into new_table. The DBMS uses the result of subquery to determine the structure of new_table and the order, names, data types, and values of its columns.

Listing 11.18 copies the structure and data of the existing table authors to a new table named authors2.

Listing 11.18Copy the structure and data of the existing table authors to a new table named authors2.

CREATE TABLE authors2 AS SELECT * FROM authors;

Listing 11.19 uses a WHERE condition that’s always false to copy only the structure (but not the data) of the existing table publishers to a new table named publishers2.

Listing 11.19Copy the structure (but not the data) of the existing table publishers to a new table named publishers2.

CREATE TABLE publishers2 AS SELECT * FROM publishers WHERE 1 = 2;

Listing 11.20 creates a global temporary table named titles2 that contains the titles and sales of books published by publisher P01; see “Creating a Temporary Table with CREATE TEMPORARY TABLE” earlier in this chapter.

Listing 11.20Create a global temporary table named titles2 that contains the titles and sales of books published by publisher P01.

CREATE GLOBAL TEMPORARY TABLE titles2 AS SELECT title_name, sales FROM titles WHERE pub_id = 'P01';

Listing 11.21 uses joins to create a new table named author_title_names that contains the names of the authors who aren’t from New York State or California and the titles of their books.

Listing 11.21Create a new table named author_title_names that contains the names of the authors who aren’t from New York state or California and the titles of their books.

CREATE TABLE author_title_names AS SELECT a.au_fname, a.au_lname, t.title_name FROM authors a INNER JOIN title_authors ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE a.state NOT IN ('CA', 'NY');

Tips for CREATE TABLE AS

To examine the new table’s structure, use one of the commands described in “Displaying Table Definitions” in Chapter 10.
It’s common to create a temporary table that contains data for the current date. For example:

CREATE GLOBAL TEMPORARY TABLE sales_today AS SELECT * FROM orders WHERE order_date = CURRENT_DATE;
The SQL:2003 standard introduced CREATE TABLE AS, but Microsoft Access and Microsoft SQL Server use SELECT INTO to create a new table from an existing one:

SELECT columns   INTO new_table   FROM existing_table   [WHERE search_condition];

The SQL standard’s version of SELECT INTO isn’t the same thing—it selects a value into a scalar variable in a host program rather than creating a new table. The Oracle, Db2, and MySQL implementations of SELECT INTO work in the standard way. For portability, you shouldn’t use CREATE TABLE AS or SELECT INTO. Instead, create a new, empty table with CREATE TABLE and then populate it with INSERT SELECT.

To run Listings 11.18 through 11.21 in Microsoft Access, type (Listing 11.18):

SELECT *   INTO authors2   FROM authors;

and (Listing 11.19):

SELECT *   INTO publishers2   FROM publishers   WHERE 1=2;

and (Listing 11.20):

SELECT title_name, sales   INTO titles2   FROM titles   WHERE pub_id='P01';

and (Listing 11.21):

SELECT a.au_fname, a.au_lname, t.title_name   INTO author_title_names   FROM titles t   INNER JOIN (authors a     INNER JOIN title_authors ta       ON a.au_id = ta.au_id)     ON t.title_id = ta.title_id   WHERE a.state NOT IN ('NY','CA');

To run Listings 11.18 through 11.21 in Microsoft SQL Server, type (Listing 11.18):

SELECT *   INTO authors2   FROM authors;

and (Listing 11.19):

SELECT *   INTO publishers2   FROM publishers   WHERE 1=2;

and (Listing 11.20):

SELECT title_name, sales   INTO ##titles2   FROM titles   WHERE pub_id = 'P01';

and (Listing 11.21):

SELECT a.au_fname, a.au_lname, t.title_name   INTO author_title_names   FROM authors a   INNER JOIN title_authors ta     ON a.au_id = ta.au_id   INNER JOIN titles t     ON ta.title_id = t.title_id   WHERE a.state NOT IN ('CA', 'NY');

In Oracle 8i, use WHERE syntax instead of JOIN syntax in Listing 11.21:

CREATE TABLE author_title_names AS   SELECT a.au_fname, a.au_lname, t.title_name   FROM authors a, title_authors ta, titles t   WHERE a.au_id = ta.au_id     AND ta.title_id = t.title_id     AND a.state NOT IN ('CA', 'NY');

Db2’s CREATE TABLE AS syntax is:

CREATE TABLE new_table AS   (subquery) options;

The Db2 documentation describes the available options. To run Listing 11.19, for example, type:

CREATE TABLE publishers2 AS   (SELECT * FROM publishers)   WITH NO DATA;

Db2 also supports

CREATE TABLE new_table LIKE existing_table

to use one table as the pattern for creating another.

To run Listing 11.20 in MySQL, delete the keyword GLOBAL.

PostgreSQL also lets you use SELECT INTO to define a new table from a query result but recommends that you use CREATE TABLE AS.

CREATE TABLE AS is similar to what some vendors call materialized tables or materialized views, except that the standard’s statement doesn’t create a linkage between the new and old tables.

Altering a Table with ALTER TABLE

Use the ALTER TABLE statement to modify a table definition by adding, altering, or dropping columns and constraints.

Despite the SQL standard, the implementation of ALTER TABLE varies greatly by DBMS. To determine what you can alter and the conditions under which alterations are allowed, search your DBMS documentation for ALTER TABLE. Depending on your DBMS, some of the modifications that you can make by using ALTER TABLE are:

Add or drop a column
Alter a column’s data type
Add, alter, or drop a column’s default value or nullability constraint
Add, alter, or drop column or table constraints such as primary-key, foreign-key, unique, and check constraints
Rename a column
Rename a table

To alter a table:

Type:

ALTER TABLE table alter_table_action;

table is the name of the table to alter. alter_table_action is a clause that specifies the action to take and begins with the keyword ADD, ALTER, or DROP. Some example actions are:

ADD COLUMN column type [constraints]

ALTER COLUMN column SET DEFAULT expr

DROP COLUMN column [RESTRICT|CASCADE]

ADD table_constraint

DROP CONSTRAINT constraint_name

Listings 11.22 and 11.23 add and drop the column email_address from the table authors.

Listing 11.22Add the column email_address to the table authors.

ALTER TABLE authors ADD email_address CHAR(25);

Listing 11.23Drop the column email_address from the table authors.

ALTER TABLE authors DROP COLUMN email_address;

If your DBMS’s ALTER TABLE statement doesn’t support an action that you need (such as, say, dropping or renaming a column or constraint), then check whether your DBMS offers the action in a different SQL statement or as a separate (non-SQL) command via the command line or graphical user interface. As a last resort, you can re-create and repopulate the table in its desired state manually.

To re-create and repopulate a table:

Use CREATE TABLE to create a new table with the new column definitions, column constraints, and table constraints; see “Creating a New Table with CREATE TABLE” and subsequent sections earlier in this chapter.
Use INSERT SELECT to copy rows (from the appropriate columns) from the old table into the new table; see “Inserting Rows with INSERT” in Chapter 10.
Use SELECT * FROM new_table to confirm that the new table has the proper rows; see “Retrieving Columns with SELECT and FROM” in Chapter 4.
Use DROP TABLE to drop the old table; see “Dropping a Table with DROP TABLE” later in this chapter.
Rename the new table to the name of the old table; see the DBMS tip in “Tips for ALTER TABLE”.
Re-create indexes as needed; see “Creating an Index with CREATE INDEX” in Chapter 12.

You also need to re-create any other properties that were dropped along with the old table, such as permissions and triggers.

Tips for ALTER TABLE

To see the result of an ALTER TABLE statement, examine the table’s structure by using one of the commands described in “Displaying Table Definitions” in Chapter 10.
You can’t drop a table’s only remaining column.
To alter or drop a constraint, use the name that you specified in the CONSTRAINT clause when you created the constraint; see “Understanding Constraints” earlier in this chapter. If you didn’t name the constraint, then use the constraint name that your DBMS generated automatically.
DBMSs typically enforce fewer modification restrictions on empty tables than they do on populated tables. When you add a new column to a table that already has one or more rows, for example, that column can’t have a NOT NULL constraint, whereas a new column in an empty table can be non-nullable.

Older versions of Db2 won’t let you drop a column with ALTER TABLE, and so won’t run Listing 11.23.

Table 11.2 lists the commands that rename tables in the current database.

Table 11.2Renaming Tables
DBMS	Command
Access	Right-click a table in the Navigation pane and then click Rename
SQL Server	EXEC sp_rename 'old_name', 'new_name'
Oracle	RENAME old_name TO new_name;
Db2	RENAME TABLE old_name TO new_name;
MySQL	RENAME TABLE old_name TO new_name;
PostgreSQL	ALTER TABLE old_name RENAME TO new_name;

Dropping a Table with DROP TABLE

Use the DROP TABLE statement to remove a table from a database. When you’re dropping a table, some important considerations are:

You can drop a base table or a temporary table.
Some DBMSs let you recover a dropped table by rolling back a transaction (see Chapter 14). If a dropped table wasn’t part of a transaction, then you can restore the table from the most recent backup (although it might be out of date).
Dropping a table destroys its structure, data, indexes, constraints, permissions, and so on.
Dropping a table isn’t the same as deleting all its rows. You can empty a table of rows, but not destroy it, with DELETE FROM table;. See “Deleting Rows with DELETE” in Chapter 10.
Dropping a table doesn’t drop views that reference that table; see Chapter 13.
You’ll have problems with foreign keys or views that reference a dropped table unless they’re altered or dropped as well.

To drop a table:

Type:

DROP TABLE table;

table is the name of the table to drop (Listing 11.24).

Listing 11.24Drop the table royalties.

DROP TABLE royalties;

Tips for DROP TABLE

Some DBMSs make you drop or alter certain other properties before dropping the table itself. In Microsoft SQL Server, for example, you can’t use DROP TABLE to drop a table referenced by a FOREIGN KEY constraint until the referencing FOREIGN KEY constraint or the referencing table is dropped first.

Standard SQL lets you specify RESTRICT or CASCADE drop behavior. RESTRICT (which is safe) prevents you from dropping a table that’s referenced by views or other constraints. CASCADE (which is dangerous) causes referencing objects to be dropped along with the table. To find out whether your DBMS supports this feature or a similar one, search your DBMS documentation for DROP TABLE.