SQLite Forum

DROP VIEW IF EXISTS fails if a TABLE of that name exists, and vice versa
Login
`-- Script started`<p>
`DROP TABLE IF EXISTS "xxx" ;`<p>
`-- No error`<p>
`DROP VIEW IF EXISTS "xxx" ;`<p> 
`-- No error`<p>
`DROP TABLE IF EXISTS "yyy" ;`<p>
`-- No error`<p>
`DROP VIEW IF EXISTS "yyy" ;`<p>
`-- No error`<p>
`CREATE TABLE "xxx" (col1, col2, col3) ;`<p>
`-- No error`<p>
`CREATE VIEW "yyy" AS SELECT * FROM "xxx" ;`<p>
`-- No error`<p>
`DROP VIEW IF EXISTS "xxx" ;`<p>
`-- Error: use DROP TABLE to delete table xxx Unable to execute statement.`<p>
`DROP TABLE IF EXISTS "yyy" ;`<p>
`-- Error: use DROP VIEW to delete view yyy Unable to execute statement.`<p>
I don't think that either of these error messages is justified. `DROP VIEW IF EXISTS` should succeed if no view of that name exists.

`https://www.sqlite.org/lang_naming.html` says "When searching database schemas for a named object, objects of types that cannot be used in the context of the reference are always ignored." A DROP VIEW statement drops a view, and a table name cannot logically be used there.

If I try to create a view and a table of that name exists, it will fail, and it I try to create a table and a view of that name exists, it will also fail. IMHO it's perfectly reasonable to DROP either of them which exists as I do at the start of the script. This is part of a set of tests which create lots of tables and views, and I really don't think that I should have to remember which of them I have created earlier.

Should you argue that at the end of each test I can drop any views and tables that I have created, I can't do that in the script because I need to look at the result of the test first.