Statements › DROP TABLE

DROP TABLE

The DROP TABLE data description statement deletes the definition of a base table from the dictionary.

When deleting a table definition, CA IDMS also deletes:

• The data contained in the table
• The CALC key, if any, defined on the table
• Any indexes defined on the table
• Optionally, referential constraints in which the table participates and views derived from the table

Authorization

To issue a DROP TABLE statement, you must own or have the DROP privilege on the table named in the statement.

Note: You need no additional privileges to issue a DROP TABLE statement with the CASCADE parameter.

Syntax

►►─── DROP TABLE ─┬────────────────┬─ table-identifier ───────────────────────►
                  └─ schema-name. ─┘

 ►─┬───────────┬──────────────────────────────────────────────────────────────►◄
   └─ CASCADE ─┘

Parameters

table-identifier

Specifies the name of the table being dropped. Table-identifier must identify a base table defined in the dictionary.

schema-name

Identifies the schema associated with the named table.

If you do not specify a schema-name, the default value is:

• The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
• The schema associated with the access module used at runtime, if the statement is embedded in an application program

CASCADE

Directs CA IDMS to delete the definitions of:

• All referential constraints where the named table is the referencing table or the referenced table
• All views derived from the named table

If you specify CASCADE in a DROP TABLE statement for a table that participates in a linked referential constraint, CA IDMS updates the rows of the other table to remove the physical links with the table being dropped.

Usage

Tables in the SYSTEM Schema

You cannot delete the definition of a table in the SYSTEM schema.

Tables in Views or Referential Constraints

If you do not specify CASCADE in a DROP TABLE statement, the table named in the statement cannot participate in the definition of any view or referential constraint.

Example

Dropping a Table that Contains Data

The following DROP TABLE statement deletes the definition of the OFFICE_POOL table and any data associated with the table. If the table participates in any referential constraint or view definitions, CA IDMS returns an error.

drop table office_pool;

Note: For more information about defining tables, see ALTER TABLE and CREATE TABLE.