{"id":462,"date":"2024-08-28T08:50:41","date_gmt":"2024-08-28T08:50:41","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=462"},"modified":"2025-06-01T11:48:09","modified_gmt":"2025-06-01T11:48:09","slug":"drop","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/drop\/","title":{"rendered":"DROP"},"content":{"rendered":"\n

SQL DROP Keyword<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL DROP COLUMN Syntax<\/h2>\n\n\n\n
ALTER TABLE table_name\nDROP COLUMN column_name;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP COLUMN Example<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP COLUMN contact_number;<\/pre><\/div>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP COLUMN contact_number;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> This command is not supported in SQLite including our TechKubo playground. SQLite does not support dropping a column directly \u2014 the table must be recreated without the column.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP a UNIQUE Constraint Example<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP CONSTRAINT UC_Patient;<\/pre><\/div>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP INDEX UC_Patient;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> Dropping constraints is not supported in SQLite. Constraints must be defined during table creation and cannot be removed afterward.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP a PRIMARY KEY Constraint Example<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP CONSTRAINT PK_Patient;<\/pre><\/div>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP PRIMARY KEY;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> This will not work in SQLite. Primary key constraints cannot be dropped separately after table creation in SQLite.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP a FOREIGN KEY Constraint Example<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong><\/p>\n\n\n\n
ALTER TABLE Appointments\nDROP CONSTRAINT FK_AppointmentPatient;<\/pre><\/div>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE Appointments\nDROP FOREIGN KEY FK_AppointmentPatient;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> SQLite does not support dropping foreign keys after table creation.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP a CHECK Constraint Example<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong><\/p>\n\n\n\n
ALTER TABLE MedicalRecords\nDROP CONSTRAINT CHK_Temperature;<\/pre><\/div>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE MedicalRecords\nDROP CHECK CHK_Temperature;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> SQLite does not support removing CHECK constraints using ALTER TABLE. They must be defined during table creation.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP DEFAULT Example<\/h2>\n\n\n\n
SQL Server \/ Oracle \/ MS Access<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nALTER COLUMN gender DROP DEFAULT;<\/pre><\/div>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nALTER gender DROP DEFAULT;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> SQLite does not support dropping default value. Defaults must be set during table creation and cannot be altered later.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP INDEX Example<\/h2>\n\n\n\n
MySQL<\/strong><\/p>\n\n\n\n
ALTER TABLE Patients\nDROP INDEX idx_lastname;<\/pre><\/div>\n\n\n\n
MS Access<\/strong><\/p>\n\n\n\n
DROP INDEX idx_lastname ON Patients;<\/pre><\/div>\n\n\n\n
SQL Server<\/strong><\/p>\n\n\n\n
DROP INDEX Patients.idx_lastname;<\/pre><\/div>\n\n\n\n
DB2 \/ Oracle<\/strong><\/p>\n\n\n\n
DROP INDEX idx_lastname;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> Dropping indexes is supported in some systems.<\/p>\n<\/blockquote>\n\n\n\n
SQLite allows dropping indexes with this command:<\/strong><\/p>\n\n\n\n
DROP INDEX index_name;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP DATABASE Syntax<\/h2>\n\n\n\n
DROP DATABASE database_name;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP DATABASE Example<\/h2>\n\n\n\n
DROP DATABASE testDB;<\/pre><\/div>\n\n\n\n
\n
Note:<\/em><\/strong> This command is not supported in SQLite. In SQLite, you delete a database by deleting its file from the system manually.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP TABLE Syntax<\/h2>\n\n\n\n
DROP TABLE table_name;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP TABLE Example<\/h2>\n\n\n\n
DROP TABLE Students;<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP VIEW Syntax<\/h2>\n\n\n\n
DROP VIEW view_name;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP VIEW Example<\/h2>\n\n\n\n
This example shows how to drop a view when you created one.<\/p>\n\n\n\n
-- Step 1: Create a view\nCREATE VIEW PatientsPH AS\nSELECT first_name, last_name, gender\nFROM Patients\nWHERE address LIKE '%Philippines%';\n\n-- Step 2: Drop the view\nDROP VIEW PatientsPH;<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP Labs<\/h2>\n\n\n\n