11.1.15 Deleting base tables

To delete a base table, execute the DROP TABLE definition SQL statement.

Important

• If you delete a base table, viewed tables that depend on it are deleted (or invalidated). Therefore, before you delete a base table, check whether there are viewed tables that depend on it, and make sure that there is no problem if those viewed tables are deleted or invalidated.

• If you delete a base table for which the primary key is defined, foreign keys that use the deleted base table as the referenced table are also deleted. (Foreign keys for other schemas are also deleted.) Therefore, before you delete a base table, make sure that there is no problem if the foreign keys are deleted. However, if you specify RESTRICT as the drop behavior, an error occurs during execution of the SQL statement.

The following shows an example of base table deletion.

Example

This example deletes a shop table (SHOPSLIST).

Procedure:

1. Check the viewed tables that depend on the base table to be deleted.

   By retrieving data from a dictionary table, you can check the viewed tables that depend on the base table to be deleted. For an example of retrieving data from a dictionary table, see 11.2.11 Checking dependent viewed tables.

2. Check the foreign keys that use the base table to be deleted as the referenced table.

   This step is required for deleting a base table for which the primary key is defined.

   By retrieving data from a dictionary table, you can check the foreign keys that use the base table to be deleted as the referenced table. For an example of retrieving data from a dictionary table, see (21) When checking foreign keys that reference a primary key in B.22 Searching a dictionary table.

3. Check whether the base table to be deleted is in the non-updatable status.

   Before you delete the base table, check whether you need to re-execute the command for that base table (whether the base table is non-updatable). For details about the check method, see (1) Checking whether a base table is non-updatable in 10.9.2 Checking the status and usage of a base table.

   Important

   If re-execution of the command is necessary

   Re-execute the command. Then, delete the base table.

   If you deleted the base table when command re-execution was necessary (when the base table was non-updatable)

   Temporary work files created by the interrupted command might remain. To delete the temporary work files, you must release all base tables from the non-updatable status. For details about deleting temporary work files, see (2) When there are unneeded temporary work files on the disk in 15.2.5 Steps to take in the event of a shortage of disk space for storing temporary work files during command execution.

4. Execute the DROP TABLE statement.

   DROP TABLE "SHOPSLIST" CASCADE

   Note

   In the preceding example, CASCADE is specified for the drop behavior. Therefore, all viewed tables that depend on the base table to be deleted are deleted. If you omit specifying the drop behavior, all viewed tables that depend on the base table to be deleted are invalidated.

To Page Top