Description

The SQL keyword DROP is used to delete an entity from a database system, which can be a database, table, column, etc.,

It is used together with other keywords as mentioned below.

Keyword Description
DROP DATABASE Deletes a database and its contents permanently from a database system.
DROP TABLE Deletes a table and its contents permanently from a database.
DROP COLUMN Deletes a column and its contents permanently from a table.
DROP VIEW Deletes a view and its contents permanently from a database system.
DROP INDEX Deletes an index and its contents permanently from a database.
DROP CONSTRAINT Deletes a constraint and its contents permanently from a table.
DROP DEFAULT Deletes a DEFAULT constraint permanently from a table.

DROP DATABASE

The SQL keyword DROP DATABASE is used to delete an existing database, which deletes the database and its contents permanently from a database system.

The below SQL deletes an existing database.

DROP DATABASE test_db;

DROP TABLE

The SQL keyword DROP TABLE is used to delete an existing table, which deletes the table and its contents permanently from a database.

The below SQL deletes an existing table.

DROP TABLE customers;

DROP COLUMN

The SQL keyword DROP COLUMN is used to delete an existing column, which deletes the column and its contents permanently from a table.

The below SQL deletes an existing column.

ALTER TABLE customers
DROP COLUMN address;

DROP VIEW

The SQL keyword DROP VIEW is used to delete an existing view, which deletes the view and its contents permanently from a database system.

  • A view name with spaces must be enclosed within double quotes or square brackets.

The below SQL deletes an existing view, with the name "Spain Customers".

DROP VIEW [Spain Customers];

DROP INDEX

The SQL keyword DROP INDEX is used to delete an existing index, which deletes the index permanently from a database.

The below SQL deletes an existing index.

Database System Syntax
MySQL ALTER TABLE table_name
DROP INDEX index_name;
SQL Server DROP INDEX table_name.index_name;
Oracle / DB2 DROP INDEX index_name;
MS Access DROP INDEX index_name ON table_name;

DROP CONSTRAINT

The SQL keyword DROP CONSTRAINT is used to delete an existing constraint, which deletes the constraint permanently from a table.

  • This keyword is not used in MySQL but used in database systems like SQL Server, Oracle, and MS Access.
  • In MySQL, it uses some other keywords to delete constraints.
    • The keyword DROP INDEX is used to delete a UNIQUE constraint.
    • The keyword DROP PRIMARY KEY is used to delete a PRIMARY KEY constraint.
    • The keyword DROP FOREIGN KEY is used to delete a FOREIGN KEY constraint.
    • The keyword DROP CHECK is used to delete a CHECK constraint.
    • The keyword DROP DEFAULT is used to delete a DEFAULT constraint.

The below table lists the syntactical differences between the database systems.

DROP Constraint SQL Server / Oracle / MS Access MySQL
UNIQUE Constraint ALTER TABLE customers
DROP CONSTRAINT UC_Customer;		 ALTER TABLE customers
DROP INDEX UC_Customer;
PRIMARY KEY Constraint ALTER TABLE customers
DROP CONSTRAINT PK_Customer;		 ALTER TABLE customers
DROP PRIMARY KEY;
FOREIGN KEY Constraint ALTER TABLE orders
DROP CONSTRAINT FK_CustomerOrder;		 ALTER TABLE orders
DROP FOREIGN KEY FK_CustomerOrder;
CHECK Constraint ALTER TABLE customers
DROP CONSTRAINT CHK_CustomerAge;		 ALTER TABLE customers
DROP CHECK CHK_CustomerAge;
DEFAULT Constraint ALTER TABLE customers
ALTER COLUMN city DROP DEFAULT;		 ALTER TABLE customers
ALTER city DROP DEFAULT;

DROP DEFAULT

The SQL keyword DROP DEFAULT is used to delete an existing DEFAULT constraint, which deletes the constraint permanently from a table.

The below table lists the syntactical differences between the database systems.

DROP Constraint SQL Server / Oracle / MS Access MySQL
DEFAULT Constraint ALTER TABLE customers
ALTER COLUMN city DROP DEFAULT;		 ALTER TABLE customers
ALTER city DROP DEFAULT;

Related Links