SQLite DROP VIEW

The DROP VIEW statement permanently removes a view definition from the database. Since a view stores only a query — not actual data — dropping a view never deletes any underlying table data. The tables the view was built on remain completely untouched.

DROP VIEW vs DROP TABLE

ACTION               WHAT IS LOST          UNDERLYING DATA
────────────────────────────────────────────────────────────
DROP VIEW myview     The saved query       Safe — tables unchanged
DROP TABLE mytable   Table + all rows      Permanent data loss

Dropping a view is always safe from a data perspective.

Basic Syntax

DROP VIEW view_name;

Safe Version with IF EXISTS

-- Without IF EXISTS: error if view does not exist
DROP VIEW active_employees;
-- Error: no such view: active_employees

-- With IF EXISTS: silent success whether view exists or not
DROP VIEW IF EXISTS active_employees;

Step-by-Step Example

-- Step 1: Create a view
CREATE VIEW IF NOT EXISTS top_earners AS
SELECT name, salary
FROM employees
WHERE salary > 80000;

-- Step 2: Confirm it exists
SELECT name FROM sqlite_master WHERE type = 'view';
name
────────────
top_earners

-- Step 3: Query the view
SELECT * FROM top_earners;
name  │ salary
──────┼────────
Alice │ 85000
Carol │ 91000

-- Step 4: Drop the view
DROP VIEW IF EXISTS top_earners;

-- Step 5: Confirm it is gone
SELECT name FROM sqlite_master WHERE type = 'view';
(no rows)

-- Step 6: Underlying data is untouched
SELECT name, salary FROM employees WHERE salary > 80000;
name  │ salary
──────┼────────
Alice │ 85000
Carol │ 91000   ← still in the table

When to Drop a View

DROP VIEW when:
──────────────────────────────────────────────────
✔ The view definition is outdated (columns changed)
✔ Replacing a view with an updated version
✔ Cleaning up temporary reporting views
✔ Renaming a view (drop old, create new)

Updating a View (Drop and Recreate)

SQLite has no ALTER VIEW or CREATE OR REPLACE VIEW statement. To update a view, drop it and recreate it with the new definition.

-- Old view: only active employees
CREATE VIEW active_employees AS
SELECT name, salary FROM employees WHERE active = 1;

-- Need to add department name column
-- Step 1: Drop the old version
DROP VIEW IF EXISTS active_employees;

-- Step 2: Create the updated version
CREATE VIEW active_employees AS
SELECT e.name, d.name AS dept, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.active = 1;

Checking All Views Before Dropping

-- See all views in the database
SELECT name, sql FROM sqlite_master WHERE type = 'view';

name               │ sql
───────────────────┼─────────────────────────────────────
active_employees   │ CREATE VIEW active_employees AS ...
monthly_summary    │ CREATE VIEW monthly_summary AS ...
top_earners        │ CREATE VIEW top_earners AS ...

-- Read the view definition before dropping
SELECT sql FROM sqlite_master
WHERE type = 'view' AND name = 'top_earners';

Views Depending on Other Views

SQLite allows views to reference other views. When you drop a view that another view depends on, the dependent view still exists but produces an error when queried.

-- base_view
CREATE VIEW base_view AS SELECT * FROM employees WHERE active = 1;

-- dependent_view references base_view
CREATE VIEW dependent_view AS
SELECT name FROM base_view WHERE salary > 70000;

-- Drop base_view
DROP VIEW IF EXISTS base_view;

-- Querying dependent_view now fails:
SELECT * FROM dependent_view;
-- Error: no such table: base_view

-- Fix: drop dependent_view first, then base_view
DROP VIEW IF EXISTS dependent_view;
DROP VIEW IF EXISTS base_view;

Key Takeaways

• DROP VIEW view_name removes the saved query definition only
• No underlying table data is ever lost when dropping a view
• Use IF EXISTS to prevent errors when the view may not exist
• Update a view by dropping and recreating it — there is no ALTER VIEW in SQLite
• Drop dependent views before the views they reference to avoid broken dependencies