DROP

SQL DROP Keyword

The DROP command is used to delete database objects such as columns, tables, constraints, views, and indexes. Use DROP with caution as it permanently removes the specified object and any data associated with it.

SQL DROP COLUMN Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name

DROP COLUMN column_name;

SQL DROP COLUMN Example

SQL Server / Oracle / MS Access

ALTER TABLE Patients
DROP COLUMN contact_number;

ALTER TABLE Patients

DROP COLUMN contact_number;

MySQL

ALTER TABLE Patients
DROP COLUMN contact_number;

ALTER TABLE Patients

DROP COLUMN contact_number;

Note: This command is not supported in SQLite including our TechKubo playground. SQLite does not support dropping a column directly — the table must be recreated without the column.

SQL DROP a UNIQUE Constraint Example

SQL Server / Oracle / MS Access

ALTER TABLE Patients
DROP CONSTRAINT UC_Patient;

ALTER TABLE Patients

DROP CONSTRAINT UC_Patient;

MySQL

ALTER TABLE Patients
DROP INDEX UC_Patient;

ALTER TABLE Patients

DROP INDEX UC_Patient;

Note: Dropping constraints is not supported in SQLite. Constraints must be defined during table creation and cannot be removed afterward.

SQL DROP a PRIMARY KEY Constraint Example

SQL Server / Oracle / MS Access

ALTER TABLE Patients
DROP CONSTRAINT PK_Patient;

ALTER TABLE Patients

DROP CONSTRAINT PK_Patient;

MySQL

ALTER TABLE Patients
DROP PRIMARY KEY;

ALTER TABLE Patients

DROP PRIMARY KEY;

Note: This will not work in SQLite. Primary key constraints cannot be dropped separately after table creation in SQLite.

SQL DROP a FOREIGN KEY Constraint Example

SQL Server / Oracle / MS Access

ALTER TABLE Appointments
DROP CONSTRAINT FK_AppointmentPatient;

ALTER TABLE Appointments

DROP CONSTRAINT FK_AppointmentPatient;

MySQL

ALTER TABLE Appointments
DROP FOREIGN KEY FK_AppointmentPatient;

ALTER TABLE Appointments

DROP FOREIGN KEY FK_AppointmentPatient;

Note: SQLite does not support dropping foreign keys after table creation.

SQL DROP a CHECK Constraint Example

SQL Server / Oracle / MS Access

ALTER TABLE MedicalRecords
DROP CONSTRAINT CHK_Temperature;

ALTER TABLE MedicalRecords

DROP CONSTRAINT CHK_Temperature;

MySQL

ALTER TABLE MedicalRecords
DROP CHECK CHK_Temperature;

ALTER TABLE MedicalRecords

DROP CHECK CHK_Temperature;

Note: SQLite does not support removing CHECK constraints using ALTER TABLE. They must be defined during table creation.

SQL DROP DEFAULT Example

SQL Server / Oracle / MS Access

ALTER TABLE Patients
ALTER COLUMN gender DROP DEFAULT;

ALTER TABLE Patients

ALTER COLUMN gender DROP DEFAULT;

MySQL

ALTER TABLE Patients
ALTER gender DROP DEFAULT;

ALTER TABLE Patients

ALTER gender DROP DEFAULT;

Note: SQLite does not support dropping default value. Defaults must be set during table creation and cannot be altered later.

SQL DROP INDEX Example

MySQL

ALTER TABLE Patients
DROP INDEX idx_lastname;

ALTER TABLE Patients

DROP INDEX idx_lastname;

MS Access

DROP INDEX idx_lastname ON Patients;

DROP INDEX idx_lastname ON Patients;

SQL Server

DROP INDEX Patients.idx_lastname;

DROP INDEX Patients.idx_lastname;

DB2 / Oracle

DROP INDEX idx_lastname;

DROP INDEX idx_lastname;

Note: Dropping indexes is supported in some systems.

SQLite allows dropping indexes with this command:

DROP INDEX index_name;

DROP INDEX index_name;

SQL DROP DATABASE Syntax

DROP DATABASE database_name;

DROP DATABASE database_name;

SQL DROP DATABASE Example

DROP DATABASE testDB;

DROP DATABASE testDB;

Note: This command is not supported in SQLite. In SQLite, you delete a database by deleting its file from the system manually.

SQL DROP TABLE Syntax

DROP TABLE table_name;

DROP TABLE table_name;

SQL DROP TABLE Example

DROP TABLE Students;

DROP TABLE Students;

SQL DROP VIEW Syntax

DROP VIEW view_name;

DROP VIEW view_name;

SQL DROP VIEW Example

This example shows how to drop a view when you created one.

-- Step 1: Create a view
CREATE VIEW PatientsPH AS
SELECT first_name, last_name, gender
FROM Patients
WHERE address LIKE '%Philippines%';

-- Step 2: Drop the view
DROP VIEW PatientsPH;

-- Step 1: Create a view

CREATE VIEW PatientsPH AS

SELECT first_name, last_name, gender

FROM Patients

WHERE address LIKE '%Philippines%';

​

-- Step 2: Drop the view

DROP VIEW PatientsPH;

SQL DROP Labs