Using the DROP TABLE Statement to Remove a Table from the Database

When you no longer need a table, use the DROP TABLE statement to remove it from the database. Before dropping a table, however, make sure that you no longer need it! When the DBMS executes the DROP TABLE statement, it erases the table data and index(es) from the database and removes the definition of the table and its constraints from the system tables. Thus, the only way to recover a dropped table is to re-create the table and restore data by reading it from the most recent backup. As such, make sure you really no longer need the table or its data before you execute the DROP TABLE statement.

The syntax of the DROP TABLE statement is:

DROP TABLE

[[<schema name>.]<table owner name>.]]<table name>

Thus, to drop the CUSTOMER table in Tip 62, "Using the CREATE TABLE Statement to Assign Foreign Key Constraints," you would execute the SQL statement:

DROP TABLE customer

When it receives the DROP TABLE command, the DBMS checks to see if the table you want to drop is referenced by a FOREIGN KEY in another table. If it is (as is the case with CUSTOMER in the current example), the DROP TABLE statement will fail, and the DBMS will display an error message similar to:

Server: Msg 3726, Level 16, State 1, Line1

Could not drop object 'customer' because it is referenced

by a FOREIGN KEY constraint

Before you can remove a table reference by a FOREIGN KEY, you must first use the ALTER TABLE statement to remove the FOREIGN KEY constraint from the other table. In the current example, you must execute the ALTER TABLE statement

ALTER TABLE order DROP CONSTRAINT fk_customer_table

before the DBMS will let you drop the CUSTOMER table.

Note While the DBMS checks its system tables for FOREIGN KEY references to the table you want to remove, it does not check VIEWs (which you learned about in Tip 11, "Understanding Views") and stored procedures to see if they reference the table or its columns. Stored procedures that reference a dropped table will fail to run, and the DBMS will return an error message in place of VIEWs data that includes columns from a dropped table. As such, check your database documentation carefully to make sure that you are the only one that uses the table you are about to drop-before you drop the table.

Using the CREATE TABLE Statement to Assign Foreign Key Constraints

As was previously discussed, a database key uniquely identifies a row in a table. In Tip 61, "Using the CREATE TABLE Statement to Assign the Primary Key," you learned that each row in a PRIMARY KEY uniquely identifies single row within the table in which the PRIMARY KEY is declared. A FOREIGN KEY, on the other hand, references the PRIMARY KEY in a table other than the one in which the FOREIGN KEY is declared. As such, each row within a FOREIGN KEY in one table uniquely identifies a single row in another table. While each PRIMARY KEY value must be unique within a table, the values within a FOREIGN KEY need not be (and most likely is not) unique.

A FOREIGN KEY is normally represents a parent/child relationship between two tables. When you place a FOREIGN KEY constraint on a column or combination of columns, you are saying that the value in a column (or combination of columns) within the row in the child table can be found in the column (or combination of columns) that makes up the PRIMARY KEY value of a specific row within the parent table.

The syntax of the FOREIGN KEY constraint declaration is:

[CONSTRAINT <constraint name>]

FOREIGN KEY (<column name>

[,<column name>...[,<last column name>]])

REFERENCES <foreign table name>

(<foreign table column name>

[,<foreign table column name>...

[,<last foreign table column name>]]}

Suppose, for example, that you want to track customer orders using the CUSTOMER (parent) table and ORDER (child) table created by:

CREATE TABLE customer

(customer_number INTEGER,

first_name VARCHAR(20),

last_name VARCHAR(30),

address VARCHAR(35),

CONSTRAINT pk_customer_table

PRIMARY KEY (customer_number))

CREATE TABLE order

(placed_by_customer_num INTEGER

FOREIGN KEY (placed_by_customer_num) REFERENCES

customer(customer_number),

order_date DATETIME,

item_number INTEGER,

quantity SMALLINT,

CONSTRAINT pk_order_table

PRIMARY KEY

(placed_by_customer_num, order_date, item_number))

The FOREIGN KEY defined in the ORDER table tells you that you can find the value in the PLACED_BY_CUSTOMER_NUM column (of the child table ORDER) in the PRIMARY KEY column CUSTOMER_NUMBER of a row in the CUSTOMER (parent) table.

Because the column PLACED_BY_CUST_NUM is a foreign and not a primary key in the ORDER table, you can have more than one ORDER row with the same value in the PLACED_BY_CUST_NUM column, indicating that an individual customer ordered more than one item or placed more than one order.

The FOREIGN KEY constraint on the PLACED_BY_CUSTOMER_NUM column also tells you that the value in the PLACED_BY_CUST_NUM column will appear once and only once in the CUSTOMER_NUMBER field of the CUSTOMER table (because a FOREIGN KEY in a table always refers to the PRIMARY KEY in another table). As such, you will be able to uniquely identify the customer that placed the order because the FOREIGN KEY value (PLACED_BY_CUSTOMER_NUM) in the current table uniquely identifies a row (a customer) in the foreign (CUSTOMER) table.

When you do not provide a name for a FOREIGN KEY constraint, the system will generate one for you so that it can store the constraint in its system tables. In the current example, the FOREIGN KEY in the ORDER table was not explicitly named. As such, the system will generate a name.

If you are using MS-SQL Server, you can determine the name of the FOREIGN KEY by executing the stored procedure sp_help and supplying the name of the parent table (CUSTOMER, in the current example) for <table name> in the statement:

EXEC sp_help <table name>

MS-SQL Server will respond with a description of the table and will list the FOREIGN KEY names in the "Table Is Referenced By" section of the report.

If you want to select a name for the FOREIGN KEY (instead of having the DBMS generate one), change the CREATE TABLE statement in the current example to:

CREATE TABLE order

(placed_by_customer_num INTEGER

CONSTRAINT fk_customer_table FOREIGN KEY

(placed_by_customer_num) REFERENCES

customer(customer_number),

order_date DATETIME,

item_number INTEGER,

quantity SMALLINT,

CONSTRAINT pk_order_table

PRIMARY KEY

(placed_by_customer_num, order_date, item_number))

As you learned in Tip 61, a PRIMARY KEY may consist of more than one column. When a FOREIGN KEY in one table references a composite (or multi-column) PRIMARY KEY in another table, the FOREIGN key, too, will consist of multiple columns. Suppose, for example, that the ITEM_MASTER table for the orders in the current example was defined by:

CREATE TABLE item_master

(item_number INTEGER,

vendor_id INTEGER,

quantity_on_hand SMALLINT

CONSTRAINT pk_item_master_table

PRIMARY KEY (item_number, vendor_id))

You could reference the composite PRIMARY KEY in the ITEM_MASTER table with the FOREIGN KEY constraint FK_ITEM_MASTER_TABLE using the CREATE TABLE statement:

CREATE TABLE order

(placed_by_customer_num INTEGER

order_date DATETIME,

item_number INTEGER,

vendor_id_number INTEGER,

quantity SMALLINT,

CONSTRAINT fk_item_master_table FOREIGN KEY

(item_number, vendor_id_number) REFERENCES

item_master (item_number, vendor_id),

CONSTRAINT fk_customer_table FOREIGN KEY

(placed_by_customer_num) REFERENCES

customer (customer_number),

CONSTRAINT pk_order_table

PRIMARY KEY

(placed_by_customer_num, order_date, item_number))

The important thing to know about the FOREIGN KEY constraint is that it specifies that the value in the column or combination of columns in one table must be found as the value in the PRIMARY KEY of the table, which it references. As such, if you have a single-column PRIMARY KEY, you will use a single-column FOREIGN KEY. Conversely, if you need to reference a multi-column or composite PRIMARY KEY, you will use a multi-column FOREIGN KEY.

Using the CREATE TABLE Statement to Assign the Primary Key

A key is a column or combination of columns that uniquely identifies a row in a table. As such, a key gives you way to distinguish one particular row in a table from all of the others. Because a key must be unique, you should not include NULL values in any of the columns that make up a key. Remember, the DBMS cannot make any assumptions about the actual value of NULL in a column. Thus, a row with NULL in a key column will be indistinguishable from any other row in the table because the NULL value could, in fact, be equal to the value in any other row.

Each table can have one (and only one) PRIMARY KEY. Because the PRIMARY KEY must uniquely identify each row in a table, the DBMS automatically applies the NOT NULL constraint to each of the columns that make up the PRIMARY KEY. When creating a new table, you can create a single-column PRIMARY KEY by including the key words "PRIMARY KEY" in the column definition.

The syntax of a PRIMARY KEY definition in a CREATE TABLE statement comes in one of two forms

[CONSTRAINT <constraint name>] PRIMARY KEY

if the PRIMARY KEY is an unnamed constraint defined as part of the PRIMARY KEY column's definition, or

CONSTRAINT <constraint name>

PRIMARY KEY (<column name>

[,<column_name...[,<last column name>]])

for a multiple-column (or composite) PRIMARY KEY or a single-column named PRIMARY KEY.

For example, the CREATE TABLE statement

CREATE TABLE employee

(employee_id INTEGER PRIMARY KEY,

first_name VARCHAR(20),

last_name VARCHAR(30))

identifies the EMPLOYEE_ID column as the PRIMARY KEY in the EMPLOYEE record. As such, every row in the EMPLOYEE table must have a unique, non-NULL value in the EMPLOYEE_ID column.

The system stores the PRIMARY KEY as a constraint in the system tables. As such, if you don't give the PRIMARY KEY a name, the DBMS will generate one for you. The name the system assigns becomes important to you if you ever want to drop a table's PRIMARY KEY so that you can change it. (Since a table can have only one PRIMARY KEY, you will need to drop the existing PRIMARY KEY and create a new one when if you want to change its column[s].)

Note If you are using MS-SQL Server, you can call the stored procedure sp_help to display the name that the DBMS assigned to the PRIMARY KEY by executing the command

EXEC sp_help <table name>

(where <table name> is the name of the table with the PRIMARY KEY whose name you want to know).

MS-SQL Server will respond with a description of the table identified by <table name>. Look in the index_name column of the section of the report titled PRIMARY to see the name the DBMS assigned to the PRIMARY KEY.

If you don't want the DBMS to generate its own name for the PRIMARY KEY, you can name it yourself by specifying the name when you identify the column that makes up the PRIMARY KEY. For example, to give the name pk_employee_table to the PRIMARY KEY in the current example, use the CREATE TABLE statement:

CREATE TABLE employee

(employee_id INTEGER,

CONSTRAINT pk_employee_table PRIMARY KEY,

first_name VARCHAR(20),

last_name VARCHAR(30))

Sometimes no single column in a table has a unique value in every row. Suppose, for example, that each division in your company issues its own employee numbers. Division #1 has employees #123, #124, and #126; division #2 has employees #121, #122, and #123. If you identify EMPLOYEE_ID as the PRIMARY KEY, you will be able to insert all division #1 employees into the EMPLOYEE table. However, when you try to insert division #2 employee #123, the DBMS will not allow you to do so. Because the EMPLOYEE table already has a row with an EMPLOYEE_ID of 123, the DBMS rejects your attempt to add a second row with 123 in the EMPLOYEE_ID column because EMPLOYEE_ID, the PRIMARY KEY, must be unique in each row of the table.

You can still create a PRIMARY KEY for the table where no single column is unique by identifying a set of multiple columns that, when taken together, is different in every row of the table. In the current example, you know that employee numbers are unique by division. Thus, you can use a two-column PRIMARY KEY consisting of EMPLOYEE_ID and DIVISION, such as

CREATE TABLE employee

(employee_id INTEGER,

division SMALLINT,

first_name VARCHAR(20),

last_name VARCHAR(30)

CONSTRAINT pk_employee_table

PRIMARY KEY (employee_id, division))

to create a PRIMARY KEY for the EMPLOYEE table.

Note The Placement of the PRIMARY KEY definition within the CREATE TABLE statement is not important. As such, the CREATE TABLE statement

CREATE TABLE employee

(employee_id INTEGER

CONSTRAINT pk_employee_table

PRIMARY KEY (employee_id, division),

division SMALLINT,

first_name VARCHAR(20),

last_name VARCHAR(30))

is equivalent to the one in the example just prior to this note.