SQL in a Nutshell, 2nd Edition

Name

DROP Statements

Synopsis

All of the database objects created with CREATE statements may be destroyed using complementary DROP statements. On some platforms, a ROLLBACK statement after a DROP statement will recover the dropped object. However, on other database platforms, the DROP statement is irreversible and permanent, so it is advisable to use the command with care.

Platform	Command
DB2	Supported, with variations
MySQL	Supported, with limitations
Oracle	Supported, with variations
PostgreSQL	Supported, with limitations
SQL Server	Supported, with limitations

SQL2003 Syntax

Currently, the SQL2003 standard supports the ability to drop a lot of object types that are largely unsupported by most vendors. The ANSI SQL2003 syntax follows this format:

DROP {object_type} object_name {RESTRICT | CASCADE}

Keywords

DROP object_type object_name

Irreversibly and permanently destroys the specified object, called object_name, of the type identified. The object_name does not need a schema identifier, but the current schema is then assumed. ANSI SQL2003 supports a long list of object types, each created with their own corresponding CREATE statement. CREATE statements covered in this book with corresponding DROP statements include:

DOMAIN	SCHEMA
FUNCTION	TABLE
METHOD	TRIGGER
PROCEDURE	TYPE
ROLE	VIEW

RESTRICT | CASCADE

Prevents the DROP from taking place if any dependent objects exist (RESTRICT), or causes all dependent objects to also be dropped (CASCADE). This clause is not allowed ...