5.6. Deleting Tables

The SQL standard permits one or more unwanted tables to be removed (or deleted) from a database (SAS library). During large program processes, temporary tables in the WORK library are frequently created. The creation and build-up of these tables can negatively affect memory and storage performance areas, causing potential problems due to insufficient resources. It is important from a database management perspective to be able to delete any unwanted tables to avoid these types of resource problems. Here are a few guidelines to keep in mind.

Before a table can be deleted, complete ownership of the table (that is, exclusive access to the table) should be verified. Although some SQL implementations require a table to be empty in order to delete it, the SAS implementation permits a table to be deleted with or without any rows of data in it. After a table is deleted, any references to that table are no longer recognized and will result in a syntax error. Additionally, any references to a deleted table in a view will also result in an error (see Chapter 8, “Working with Views”). Also, any indexes associated with a deleted table are automatically dropped (see Chapter 6, “Modifying and Updating Tables and Indexes”).

5.6.1. Deleting a Single Table

Deleting a table from the database environment is not the same as making a table empty. Although an empty table contains no data, it still possesses a structure; a deleted table contains no data or related structure. Essentially a deleted table does not exist because the table including its data and structure are physically removed forever. Deleting a single table from a database environment requires a single table name to be referenced in a DROP TABLE statement. In the next example, a single table called HOT_PRODUCTS located in the WORK library is physically removed using a DROP TABLE statement as follows.

SQL Code

PROC SQL;
  DROP TABLE HOT_PRODUCTS;
QUIT;

SAS Log Results

     PROC SQL;
       DROP TABLE HOT_PRODUCTS;
NOTE: Table WORK.HOT_PRODUCTS has been dropped.
     QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.38 seconds

5.6.2. Deleting Multiple Tables

The SQL standard also permits more than one table to be specified in a single DROP TABLE statement. The next example and corresponding log shows two tables (HOT_PRODUCTS and NOT_SO_HOT_PRODUCTS) being deleted from the WORK library.

SQL Code

PROC SQL;
  DROP TABLE HOT_PRODUCTS, NOT_SO_HOT_PRODUCTS;
QUIT;

SAS Log Results

     PROC SQL;
       DROP TABLE HOT_PRODUCTS, NOT_SO_HOT_PRODUCTS;
NOTE: Table WORK.HOT_PRODUCTS has been dropped.
NOTE: Table WORK.NOT_SO_HOT_PRODUCTS has been dropped.
     QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.00 seconds

5.6.3. Deleting Tables Containing Integrity Constraints

As previously discussed in this chapter, to ensure a high-level of data integrity in a database environment the SQL standard permits the creation of one or more integrity constraints to be imposed on a table. Under the SQL standard, a table containing one or more constraints cannot be deleted without first dropping the defined constraints. This behavior further safeguards and prevents the occurrence of unanticipated surprises such as the accidental deletion of primary or supporting tables.

In the next example, the SAS log shows that an error is produced when an attempt to drop a table containing an ON DELETE RESTRICT referential integrity constraint is performed. The referential integrity constraint caused the DROP TABLE statement to fail resulting in the INVENTORY table not being deleted.

SAS Log

   PROC SQL;
     DROP TABLE INVENTORY;
ERROR: A rename/delete/replace attempt is not
 allowed for a data set involved in a referential
 integrity constraint. WORK.INVENTORY.DATA
WARNING: Table WORK.INVENTORY has not been dropped.
   QUIT;
NOTE: The SAS System stopped processing this step
 because of errors.
NOTE: PROCEDURE SQL used:
      real time           0.00 seconds
      cpu time            0.00 seconds

To enable the deletion of a table containing one or more integrity constraints, you must specify an SQL statement such as the ALTER TABLE statement and DROP COLUMN or DROP CONSTRAINT clauses. Once a table’s integrity constraints are removed, the table can then be deleted.

In the following SAS log, the FOREIGN_PRODUCT_KEY constraint is removed from the INVENTORY table using the DROP CONSTRAINT clause. With the constraint removed, the INVENTORY table is then deleted with the DROP TABLE statement.

SAS Log Results

   PROC SQL;
     ALTER TABLE INVENTORY
       DROP CONSTRAINT FOREIGN_PRODUCT_KEY;
NOTE: Integrity constraint FOREIGN_PRODUCT_KEY
 deleted.
NOTE: Table WORK.INVENTORY has been modified, with
 5 columns.
   QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.01 seconds
      cpu time            0.01 seconds


   PROC SQL;
     DROP TABLE INVENTORY;
NOTE: Table WORK.INVENTORY has been dropped.
   QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.34 seconds
      cpu time            0.02 seconds