SQLite Forum

DROP VIEW IF EXISTS fails if a TABLE of that name exists, and vice versa
Login

DROP VIEW IF EXISTS fails if a TABLE of that name exists, and vice versa

(1) By Richard PArkins (rparkins) on 2021-01-16 22:30:29 [source]

-- Script started

DROP TABLE IF EXISTS "xxx" ;

-- No error

DROP VIEW IF EXISTS "xxx" ;

-- No error

DROP TABLE IF EXISTS "yyy" ;

-- No error

DROP VIEW IF EXISTS "yyy" ;

-- No error

CREATE TABLE "xxx" (col1, col2, col3) ;

-- No error

CREATE VIEW "yyy" AS SELECT * FROM "xxx" ;

-- No error

DROP VIEW IF EXISTS "xxx" ;

-- Error: use DROP TABLE to delete table xxx Unable to execute statement.

DROP TABLE IF EXISTS "yyy" ;

-- Error: use DROP VIEW to delete view yyy Unable to execute statement.

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.

(2) By Stephan Beal (stephan) on 2021-01-16 22:40:37 in reply to 1 [link] [source]

DROP VIEW IF EXISTS "xxx" ;

This topic generated a fairly long discussion last summer over in /forumpost/daec33be27.

(3) By Richard PArkins (rparkins) on 2021-01-22 12:09:22 in reply to 2 [link] [source]

Thanks for the link. I will post in that thread.