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 ...

Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.