SQL DROP TABLE and SQL DROP DATABASE for Dropping Tables and Databases

CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50) NOT NULL,
    last_name      VARCHAR(50) NOT NULL,
    email          VARCHAR(100) UNIQUE,
    department_id  INT,
    CONSTRAINT employees_dept_fk
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE projects (
    project_id   INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    lead_id      INT,
    CONSTRAINT projects_lead_fk
        FOREIGN KEY (lead_id) REFERENCES employees(employee_id)
);

CREATE VIEW staff_directory AS
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Sample data
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Marketing');
INSERT INTO employees VALUES (101, 'Alice', 'Martin', 'alice@corp.com', 1);
INSERT INTO employees VALUES (102, 'Bob', 'Jones', 'bob@corp.com', 2);
INSERT INTO projects VALUES (1, 'Website Redesign', 101);

departments
    └─► employees (FK: department_id)
            └─► projects (FK: lead_id)

departments + employees
    └─► staff_directory (VIEW)

DROP TABLE table_name;

DROP TABLE projects;

SELECT * FROM projects;

ERROR: relation "projects" does not exist

DROP TABLE archived_logs;

ERROR: table "archived_logs" does not exist

DROP TABLE IF EXISTS archived_logs;
-- No error even if the table doesn't exist

-- Idempotent cleanup script: safe to run multiple times
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

IF OBJECT_ID('archived_logs', 'U') IS NOT NULL
    DROP TABLE archived_logs;

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE archived_logs';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

DROP TABLE departments;
-- Equivalent to:
DROP TABLE departments RESTRICT;

ERROR: cannot drop table departments because other objects depend on it
DETAIL: constraint employees_dept_fk on table employees depends on table departments
        view staff_directory depends on table departments

-- PostgreSQL
DROP TABLE departments CASCADE;

NOTICE: drop cascades to constraint employees_dept_fk on table employees
NOTICE: drop cascades to view staff_directory

-- employees still exists with its data intact
SELECT employee_id, first_name, department_id FROM employees;

-- The view is gone
SELECT * FROM staff_directory;
-- ERROR: relation "staff_directory" does not exist

-- DROP TABLE departments CASCADE:
-- ✓ Removes the FK constraint on employees
-- ✓ Drops the staff_directory view
-- ✗ Does NOT delete any rows from employees

-- Versus: DELETE FROM departments WHERE department_id = 1
-- (with ON DELETE CASCADE on the FK):
-- ✓ Deletes all employees in department 1

-- MySQL: CASCADE has NO effect
DROP TABLE departments CASCADE;
-- Still fails if other tables have FKs referencing departments

-- MySQL workaround: disable FK checks temporarily
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE departments;
SET FOREIGN_KEY_CHECKS = 1;

-- SQL Server: find dependent foreign keys
SELECT
    fk.name AS constraint_name,
    OBJECT_NAME(fk.parent_object_id) AS referencing_table
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('departments');

-- Drop each FK manually
ALTER TABLE employees DROP CONSTRAINT employees_dept_fk;

-- Now the table can be dropped
DROP TABLE departments;

-- Oracle: drops FK constraints but NOT dependent views
DROP TABLE departments CASCADE CONSTRAINTS;
-- Views that reference departments become invalid

-- PostgreSQL
SELECT
    c.relname AS dependent_object,
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        WHEN 'i' THEN 'index'
    END AS object_type
FROM pg_depend d
JOIN pg_class c ON d.objid = c.oid
WHERE d.refobjid = 'departments'::regclass
  AND c.relname != 'departments';

-- MySQL
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'departments';

-- SQL Server
SELECT
    OBJECT_NAME(parent_object_id) AS referencing_table,
    name AS constraint_name
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('departments');

DROP TABLE IF EXISTS projects, employees, departments;

DROP TABLE departments;   -- employees still references this!

ERROR: cannot drop table departments because other objects depend on it

DROP TABLE IF EXISTS projects;           -- leaf: references employees
DROP TABLE IF EXISTS employees;          -- middle: references departments
DROP TABLE IF EXISTS departments;        -- root: no remaining references

DROP TABLE IF EXISTS departments, employees, projects CASCADE;

DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

DROP DATABASE test_store;

-- PostgreSQL
\c test_store
DROP DATABASE test_store;
-- ERROR: cannot drop the currently open database

-- PostgreSQL
\c postgres
DROP DATABASE test_store;

-- SQL Server
USE master;
DROP DATABASE test_store;

DROP DATABASE test_store;
-- ERROR: database "test_store" is being accessed by other users

-- PostgreSQL 13+
DROP DATABASE test_store WITH (FORCE);

-- PostgreSQL (older)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'test_store' AND pid <> pg_backend_pid();
DROP DATABASE test_store;

-- SQL Server
ALTER DATABASE test_store SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE test_store;

-- PostgreSQL
SELECT current_database(), current_user, inet_server_addr();

-- MySQL
SELECT DATABASE(), USER(), @@hostname;

-- SQL Server
SELECT DB_NAME(), SUSER_NAME(), @@SERVERNAME;

-- PostgreSQL: abort if connected to production
DO $$
BEGIN
    IF current_database() = 'production' THEN
        RAISE EXCEPTION 'ABORT: Connected to production database!';
    END IF;
END $$;

-- Quick table-level backup
CREATE TABLE departments_backup_20240615 AS SELECT * FROM departments;

-- Verify row counts match
SELECT
    (SELECT COUNT(*) FROM departments) AS original,
    (SELECT COUNT(*) FROM departments_backup_20240615) AS backup;

pg_dump production_db > backup_20240615.sql

mysqldump production_db > backup_20240615.sql

BACKUP DATABASE production_db TO DISK = 'C:\backups\backup_20240615.bak';

-- PostgreSQL: DROP TABLE is transactional!
BEGIN;

DROP TABLE legacy_data;

-- Oh no, wrong table!
ROLLBACK;
-- Table is restored, crisis averted.

-- Step 1: Rename (soft drop)
ALTER TABLE legacy_data RENAME TO _drop_20240615_legacy_data;

-- Step 2: Wait a week. Monitor for errors in your application.

-- Step 3a: Nothing broke. Safe to drop.
DROP TABLE _drop_20240615_legacy_data;

-- Step 3b: Something broke! Restore instantly.
ALTER TABLE _drop_20240615_legacy_data RENAME TO legacy_data;

-- PostgreSQL: create a limited role
CREATE ROLE app_user LOGIN PASSWORD 'secure_pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- app_user cannot DROP anything

-- Only admin accounts should have DDL privileges
GRANT ALL PRIVILEGES ON DATABASE production_db TO admin_user;

-- PostgreSQL: preview what CASCADE would affect
SELECT
    deptype,
    classid::regclass,
    objid::regclass AS dependent_object
FROM pg_depend
WHERE refobjid = 'departments'::regclass
  AND deptype = 'n';

-- Explicit about what you're destroying
DROP VIEW IF EXISTS staff_directory;
ALTER TABLE employees DROP CONSTRAINT employees_dept_fk;
DROP TABLE departments;

-- Instead of the implicit:
-- DROP TABLE departments CASCADE;  -- what exactly does this destroy?

-- "I need to remove cancelled orders"
DELETE FROM orders WHERE status = 'cancelled';

-- "I need to clear the staging table before reloading"
TRUNCATE TABLE staging_imports;

-- "We decommissioned the legacy system; this table is obsolete"
DROP TABLE IF EXISTS legacy_orders;

-- Developer wants to clear test data
DROP TABLE test_orders;

-- Now they need to recreate the table with all columns, constraints, indexes...
CREATE TABLE test_orders ( ... );

-- TRUNCATE would have been simpler
TRUNCATE TABLE test_orders;
-- Table is empty but fully intact. Ready for new data immediately.

-- Step 1: Drop views (they depend on tables but nothing depends on them)
DROP VIEW IF EXISTS staff_directory;

-- Step 2: Drop leaf tables (FK references exist only outward)
DROP TABLE IF EXISTS projects;

-- Step 3: Drop intermediate tables
DROP TABLE IF EXISTS employees;

-- Step 4: Drop root tables
DROP TABLE IF EXISTS departments;

DROP TABLE IF EXISTS departments, employees, projects CASCADE;
DROP VIEW IF EXISTS staff_directory;

-- Nuclear option: destroy and recreate the entire schema
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO public;

CREATE TEMPORARY TABLE temp_report AS
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

-- Use it during your session
SELECT * FROM temp_report;

-- Automatically dropped when the session ends
-- Or drop it explicitly
DROP TABLE IF EXISTS temp_report;

DROP TABLE departments;
-- ERROR: other objects depend on it

-- MySQL: CASCADE is syntactically accepted but functionally ignored
DROP TABLE departments CASCADE;
-- Still fails if FKs reference this table

DROP TABLE audit_logs;
-- "Wait, compliance needed those for 7 years..."

-- MySQL / Oracle
BEGIN;
DROP TABLE important_data;
ROLLBACK;
-- Table is STILL GONE. DROP triggered an implicit commit.