Usage and examples of SQL DROP keyword

SQL Keyword Reference

DROP COLUMN (Delete Column)

The DROP COLUMN command is used to delete a column from an existing table.

The following SQL deletes the 'ContactName' column from the 'Customers' table:

　　ALTER TABLE Customers
DROP COLUMN ContactName;

Delete UNIQUE constraint

To delete a UNIQUE constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

MySQL:

ALTER TABLE Persons
DROP INDEX UC_Person;

Delete PRIMARY KEY constraint

To delete a PRIMARY KEY constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

Delete FOREIGN KEY constraint

To delete a FOREIGN KEY constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

Delete CHECK constraint

To delete a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Delete DEFAULT constraint

The DROP DEFAULT command is used to delete a DEFAULT constraint.

To delete a DEFAULT constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;

DROP INDEX (Delete Index)

The DROP INDEX command is used to delete an index from a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2 / Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE DROP INDEX;

DROP DATABASE (Delete Database)

The DROP DATABASE command is used to delete an existing SQL database.

The following SQL deletes the database named 'testDB':

　　DROP DATABASE testDB;

DROP TABLE (Delete Table)

The DROP TABLE command will delete a table from the database.

The following SQL deletes the table "Shippers":

　　DROP TABLE Shippers;

DROP VIEW (Delete View)

The DROP VIEW command will delete a view.

The following SQL deletes the 'Brazil Customers' view:

DROP VIEW [Brazil Customers];

SQL Keyword Reference