Informix Guide to SQL: Syntax SQL Statements

Use the DROP TABLE statement to remove a table, along with its associated indexes and data.

You must be the owner of the table or have the DBA privilege to use the DROP TABLE statement.

If you are using Enterprise Decision Server, you cannot drop a table that includes a dependent GK index unless the dependent index is entirely dependent on the affected table.

If you issue a DROP TABLE statement, DB-Access does not prompt you to verify that you want to delete an entire table.

Use the DROP TABLE statement with caution. When you remove a table, you also delete the data stored in it, the indexes or constraints on the columns (including all the referential constraints placed on its columns), any local synonyms assigned to it, any triggers created for it, and any authorizations you have granted on the table. You also drop all views based on the table and any violations and diagnostics tables associated with the table.

When you drop a table, you do not remove any synonyms for the table that were created in an external database. If you want to remove external synonyms to the dropped table, you must do so manually with the DROP SYNONYM statement.

The CASCADE mode means that a DROP TABLE statement removes related database objects, including referential constraints built on the table, views defined on the table, and any violations and diagnostics tables associated with the table.

If the table is the supertable in an inheritance hierarchy, CASCADE drops all of the subtables as well as the supertable.

The CASCADE mode is the default mode of the DROP TABLE statement. You can also specify this mode explicitly with the CASCADE keyword.

With the RESTRICT keyword, you can control the success or failure of the drop operation for supertables, for tables that have referential constraints and views defined on them, or for tables that have violations and diagnostics tables associated them. Using the RESTRICT option causes the drop operation to fail and an error message to be returned if any of the following conditions are true:

• Existing referential constraints reference table name.
• Existing views are defined on table name.
• Any violations and diagnostics tables are associated with table name.
• The table name is the supertable in an inheritance hierarchy.

Some opaque data types require special processing when they are deleted. For example, if an opaque data type contains spatial or multi-representational data, it might provide a choice of how to store the data: inside the internal structure or, for very large objects, in a smart large object.

The database server removes opaque types by calling a user-defined support function called destroy(). When you execute the DROP TABLE statement on a table whose rows contain an opaque type, the database server automatically invokes the destroy() function for the type. The destroy() function can perform certain operations on columns of the opaque data type before the table is dropped. For more information about the destroy() support function, see Extending Informix Dynamic Server 2000.

Observe the following restrictions on the types of tables that you can drop:

• You cannot drop any system catalog tables.
• You cannot drop a table that is not in the current database.
• You cannot drop a violations or diagnostics table. Before you can drop such a table, you must first issue a STOP VIOLATIONS TABLE statement on the base table with which the violations and diagnostics tables are associated.
• If you are using Enterprise Decision Server, you cannot drop a table that appears in the FROM clause of a GK index.

The following example deletes two tables. Both tables are within the current database and are owned by the current user. Neither table has a violations or diagnostics table associated with it. Neither table has a referential constraint or view defined on it.

DROP  TABLE  customer;

DROP  TABLE  stores_demo@accntg:joed.state; 

Related statements: CREATE TABLE and DROP DATABASE

For a discussion of the data integrity of tables, see the Informix Guide to SQL: Tutorial.

For a discussion of how to create a table, see the Informix Guide to Database Design and Implementation.