Dropping Views

Dropping a view removes only the stored query definition.

It does not delete rows from base tables used by that view.

Core Concepts

flowchart TD
  A[DROP VIEW statement] --> B[View definition removed]
  B --> C[Base tables remain]
  C --> D[Queries to dropped view fail]

Statement	Behavior
DROP VIEW my_view;	Removes view (errors if missing)
DROP VIEW IF EXISTS my_view;	Safe no-error removal if missing

Code Examples

-- Setup a temporary view.
CREATE TABLE orders_drop_v (
  order_id INTEGER PRIMARY KEY,
  total    REAL NOT NULL
);

INSERT INTO orders_drop_v (order_id, total)
VALUES (1, 10.0), (2, 20.0);

CREATE VIEW v_orders_drop AS
SELECT order_id, total
FROM orders_drop_v;

Expected output
Base table and view created successfully.

-- Remove the view definition safely.
DROP VIEW IF EXISTS v_orders_drop;

Expected output
View removed with no error if already absent.

-- Confirm view is gone while table data remains.
SELECT name, type
FROM sqlite_schema
WHERE name IN ('v_orders_drop', 'orders_drop_v')
ORDER BY type, name;

name	type
orders_drop_v	table

SQLite-Specific Nuances

SQLite Nuance

DROP VIEW affects only the view object in sqlite_schema.

Underlying table definitions and rows are unchanged.

Common Pitfalls / Best Practices

Pitfall

Confusing DROP VIEW and DROP TABLE, especially when names are similar.

Best Practice

Use clear naming conventions (v_ prefix or similar) so view objects are easy to identify.

Quick Challenge

Write a defensive statement to remove view v_high_value_orders only if it exists.

View Solution

DROP VIEW IF EXISTS v_high_value_orders;

Expected output
View removed safely, no error when missing.