The DROP statement removes or erases an object from the database. When an object is dropped, its description is also deleted from the system tables.
Required Privileges
To drop a database object, the user authorization must own the object, own or possess OWNER privileges on the schema to which the object belongs, or possess DBA privileges. To drop a schema, the user authorization must either own the schema or possess DBA privileges. Only a user with DBA privileges can execute the DROP AUTHORIZATION command.
Syntax
Description
This drops the specified user authorization and all associated privileges. Only a user with DBA privileges can execute this command. It is not possible to drop the user DBA or PUBLIC.
Drop behavior:
If any database objects remain that belong to the user authorization, the
DROP will fail (this is both the default and RESTRICT behavior).
Example
DROP AUTHORIZATION user1;
This command removes the authorization ID user1 from the database.
This drops the specified connection object.
Drop behavior:
If any linked table depends on the specified connection, the DROP will fail (this is both the default and RESTRICT behavior).
Example
DROP CONNECTION cn2;
This command removes the connection cn2 from the database.
This drops the specified domain. If the domain does not belong to the current schema, prefix the domain with the name of the schema to which it belongs, separating the names with a period (that is, schema-name.domain-name).
Drop behavior:
If the domain is referenced by any column within the database, the DROP will
fail (this is both the default and RESTRICT behavior).
Example
DROP DOMAIN d_name;
This command removes the domain d_name from the current schema.
This drops the specified materialized join. If the materialized join does not belong to the current schema, prefix the materialized join with the name of the schema to which it belongs, separating the names with a period (that is, schema-name.join-name).
Example
DROP MATERIALIZED JOIN s1.matjoin7;
This command removes the materialized join matjoin7 from the s1 schema.
This drops the specified schema. The user executing the DROP SCHEMA command must own the schema, or possess DBA privileges. It is not possible to drop the PUBLIC, SYSTEM, or DBA schema from the database.
Drop behavior:
If the schema contains any database objects, the DROP will fail; all objects
belonging to the schema must first be dropped (this is both the default and RESTRICT behavior).
Example
DROP SCHEMA s1;
This command removes the schema named s1 from the database.
This drops the specified snapshot table. The user executing the DROP SNAPSHOT TABLE command must own the snapshot table, or possess DBA privileges.
Drop behavior:
By default or with the RESTRICT option, if the snapshot table is referenced by a view or snapshot table definition, the DROP will fail. With the CASCADE option, objects that reference the snapshot table, such as views and other snapshot tables, will be dropped before the snapshot table itself is dropped.
When a snapshot table is dropped, all privileges on the dropped table are also dropped.
Example
DROP SNAPSHOT TABLE st1;
This command removes the snapshot table named st1 from the database.
This drops the specified table. If the table does not belong to the current schema, prefix the table with the name of the schema to which it belongs, separating the names with a period (that is, schema-name.table-name). The table cannot be a database system table.
Drop behavior:
By default or with the RESTRICT option, if the table has dependent objects (for instance, view definitions, snapshot tables, materialized joins, or FOREIGN KEY constraints), the DROP will fail. With the CASCADE option, objects that reference the table will be dropped before the table itself is dropped.
When using the CASCADE option on a partitioned table, the following objects will be removed:
- the specified partitioned table
- each linked table that was referenced in the partitioned table creation statement
- the remote tables to which the linked tables point
- the default partition of each user that had the partition table as default
Note that the connection objects used for the linked tables are not dropped with CASCADE. Those would have to be removed using separate DROP CONNECTION commands.
When any table is dropped, all privileges on the dropped table are also dropped.
Example
DROP TABLE t1;
This command removes the table t1 from the current
schema.
This drops the specified view. If the view does not belong to the current schema, prefix the view with the name of the schema to which it belongs, separating the names with a period (that is, schema-name.view-name).
Drop behavior:
By default or with the RESTRICT option, if the view is referenced by another view definition, the DROP will fail. With the CASCADE option, objects that reference the view, such as other views and snapshot tables, will be dropped before the view itself is dropped.
When a view is dropped, all privileges on the view are also dropped.
Example
DROP VIEW redparts;
This command removes the view redparts from the current schema.