SQL in a Nutshell, 3rd Edition

Name

DROP Statements

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
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 object of type object_type called object_name. The object_name does not need a schema identifier, but if none is provided the current schema is assumed. ANSI SQL2003 supports a long list of object types, each created with its own corresponding CREATE statement. CREATE statements covered in this book with corresponding DROP statements include:

• DOMAIN

• FUNCTION

• METHOD

• PROCEDURE

• ROLE

• SCHEMA

• TABLE

• TRIGGER

• TYPE

• 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 with some forms of DROP, such as DROP TRIGGER, but is mandatory for others, such ...