11. Creating, Altering, and Dropping Tables

Sections
Creating Tables
Understanding Constraints
Creating a New Table with CREATE TABLE
Forbidding Nulls with NOT NULL
Specifying a Default Value with DEFAULT
Specifying a Primary Key with PRIMARY KEY
Specifying a Foreign Key with FOREIGN KEY
Forcing Unique Values with UNIQUE
Adding a Check Constraint with CHECK
Creating a Temporary Table with CREATE TEMPORARY TABLE
Creating a New Table from an Existing One with CREATE TABLE AS
Altering a Table with ALTER TABLE
Dropping a Table with DROP TABLE

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:

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:

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:

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:

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:

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

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:

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

To specify a column’s nullability:

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

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:

To specify a column’s default value:

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

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:

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

To specify a simple primary key:

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:

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

Specifying a Foreign Key with FOREIGN KEY

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

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

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:

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:

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

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:

To specify a simple unique constraint:

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:

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

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:

To add a check constraint:

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

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:

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:

To create a temporary table:

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

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:

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

To create a new table from an existing table:

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

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:

To alter a table:

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:

  1. 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.
  2. 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.
  3. 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.
  4. Use DROP TABLE to drop the old table; see “Dropping a Table with DROP TABLE” later in this chapter.
  5. Rename the new table to the name of the old table; see the DBMS tip in “Tips for ALTER TABLE”.
  6. 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

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:

To drop a table:

Listing 11.24Drop the table royalties.

DROP TABLE royalties;

Tips for DROP TABLE