SQL Tutorial SQL Advanced SQL Database SQL References

SQL DROP Keyword



The SQL DROP keyword is used to delete a COLUMN, CONSTRAINT, DATABASE, INDEX, TABLE, or VIEW.

DROP COLUMN

To drop the Age column from table called Employee, the following SQL code can be used:

ALTER TABLE Employee
DROP COLUMN Age;

DROP a UNIQUE Constraint

To drop UC_Employee UNIQUE constraint from table called Employee, the below mentioned SQL code can be used:

MySQL

ALTER TABLE Employee
DROP INDEX UC_Employee;

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;

DROP a PRIMARY KEY Constraint

To drop PK_Employee PRIMARY KEY constraint from table called Employee, the SQL code is given below:

MySQL

ALTER TABLE Employee
DROP PK_Employee;

OR 

ALTER TABLE Employee
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT PK_Employee;

DROP a FOREIGN KEY Constraint

To drop FK_Contact_Info FOREIGN KEY constraint from table called Contact_Info, the SQL code is given below:

MySQL

ALTER TABLE Contact_Info
DROP FOREIGN KEY FK_Contact_Info;

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;

DROP a CHECK Constraint

To drop CHK_Employee CHECK constraint from table called Employee, the SQL code is given below:

MySQL

ALTER TABLE Employee
DROP CHECK CHK_Employee;

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT CHK_Employee;

DROP DEFAULT constraint

To drop DEFAULT constraint from City column of Employee table, the SQL code is given below:

MySQL

ALTER TABLE Employee
ALTER City DROP DEFAULT;

OR

ALTER TABLE Employee
ALTER COLUMN City DROP DEFAULT;

SQL Server

/* Drops a DEFAULT constraint named DV_City */
ALTER TABLE Employee
DROP CONSTRAINT DV_City;

Oracle

ALTER TABLE Employee 
MODIFY City DEFAULT NULL;

DROP INDEX

Consider a table called Employee which contains an index named idx_name. To drop this index, the following statement can be used:

MS Access / MS Access / SQL Server

DROP INDEX idx_name ON Employee;

MySQL / SQL Server

ALTER TABLE Employee
DROP INDEX idx_name;

SQL Server

DROP INDEX Employee.idx_name;

Oracle

DROP INDEX idx_name;

DROP DATABASE

To drop an existing database with name testDB, the following statement can be used:

DROP DATABASE testDB;

Note: Extra measures should be taken while dropping a database. Deleting a database will result in loss of complete information stored in the database.

DROP TABLE

To drop a table name Employee, the following statement can be used:

DROP TABLE Employee;

Note: Be careful before dropping a table. Once deleted, all information stored in that table will be lost forever!.

DROP VIEW

To drop EmployeeInIndia view from table called Employee, the below statement can be used:

DROP VIEW EmployeeInIndia;

❮ SQL Keywords