SQL DROP Statements: Deleting Database Objects

SQL provides several DROP commands to remove database objects. These are powerful commands that permanently delete data; always back up your data before using them.

DROP COLUMN

Deleting a Column from a Table

The DROP COLUMN command removes a column from an existing table. The data within the column is lost permanently. (Note: Not all database systems support this command directly; some might require alternative approaches.)

ALTER TABLE table_name
DROP COLUMN column_name;
      

Example: Removing the ContactName Column

ALTER TABLE Customers
DROP COLUMN ContactName;
      

(The ContactName column and its data are permanently deleted from the Customers table.)
      

Dropping Constraints

Dropping a UNIQUE Constraint

Removes a unique constraint. The syntax varies slightly depending on the database system.

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
      

ALTER TABLE Persons
DROP INDEX UC_Person;
      

(The unique constraint UC_Person is dropped.)
      

Dropping a PRIMARY KEY Constraint

Removes a primary key constraint.

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
      

ALTER TABLE Persons
DROP PRIMARY KEY;
      

(The primary key constraint PK_Person is dropped.)
      

Dropping a FOREIGN KEY Constraint

Removes a foreign key constraint.

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
      

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
      

(The foreign key constraint FK_PersonOrder is dropped.)
      

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
      

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
      

(The CHECK constraint CHK_PersonAge is dropped.)
      

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
      

ALTER TABLE Persons
ALTER City DROP DEFAULT;
      

(The default constraint on the City column is dropped.)
      

DROP INDEX index_name ON table_name;
      

DROP INDEX table_name.index_name;
      

DROP INDEX index_name;
      

ALTER TABLE table_name DROP INDEX index_name;
      

(The specified index is dropped from the table.)
      

DROP DATABASE database_name;
      

(The database and all its contents are permanently deleted.)
      

DROP TABLE table_name;
      

(The table and its data are permanently deleted.)
      

DROP VIEW view_name;
      

(The view is deleted.)