O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required