SQLite Forum

DROP VIEW IF EXISTS failure
Login
> That it was there makes me wonder (more than before) what you are doing, and what your use case is. Did you anticipate having table/view name collisions, and just want to clear them? If so, it is an illusory problem; it cannot exist.

It's not an illusory problem. And of course I do not want (and never wanted and will never want) to have both a view and a table with the same name.

[tl;dr] The use case (which many of you seem to be wondering about) is actually very simple: There are databases created by a software in which the database schema has changed every now and then. In former times, there existed a *table* "metadata" which has later been replaced by a table "meta" with an entirely different structure. For compatiblity purposes, a *view* "metadata" has been introduced at the same time which is defined to provide exactly the same layout as the original *table* with the same name. So any program version can work with database version. (Triggers ensure the correct mapping to "meta" when inserting or updating anything in "metadata"). We are working with these databases and want to change a *different* table now. And because SQLite doesn't simply provide "ALTER TABLE", but instead requires a 12-step update algorithm, we have to drop any views that refer to the particular table. Unfortunately, "metadata" is one of them. The problem is, I obviously have no way do drop the view only if it exists :(

> Advantages of not raising an error: View can properly be dropped even if a table with same name exists.../

This has been a typo of mine. It should, of course, read: "View-dropping can properly be *skipped* even if a table with the same name exists". Sorry for that.

> What if you said  DROP TABLE IF EXISTS jerry; while a View with the name "jerry" does exist? Would you like the DB to simply do nothing...

Sure. What else? From the documentation: `The optional IF EXISTS clause suppresses the error that would normally result if the *table* does not exist.`

> I get that it would be easier in the specific case you mention, but I certainly don't want the Engine to become forgiving of such a transgression when I'm the culprit doing the bad thing.

That's what you have "DROP VIEW" (without "IF EXISTS") for. And you'd get a notice or warning anyway even with "IF EXISTS".

The documentations of all RDBMSs that I found and that support the "IF EXISTS" extensions agree:

SQLite documentation:

`If the specified view cannot be found and the IF EXISTS clause is not present, it is an error. If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op.`

PostgreSQL documentation:

`Do not throw an error if the view does not exist. A notice is issued in this case.`

MariaDB documentation:

`The IF EXISTS clause prevents an error from occurring for views that don't exist. When this clause is given, a NOTE is generated for each non-existent view.`

SQL Server documentation:

`Conditionally drops the view only if it already exists.`

> That is however different from trying to DROP a DB object by name that DOES exist, but is not of the type you are trying to DROP (or CREATE, for that matter). The needed action in this case is to immediately notify the programmer of that mistake - and we all want to know about that mistake.

Then, as has been pointed out before, a DROP TRIGGER, DROP INDEX, etc. had to raise an error as well. Because the user *could* have wanted to delete the table instead of the index. Someone recently mentioned the SQL is a declarative language: "you say what you want accomplished, not how it must be accomplished". Here I have the point that I tell the engine exactly what I want - and get a different response.

And, by the way, for an RDBMS that is the supreme example of being forgiving on errors (some keywords: data types, using identifiers as names without quoting, double quoted strings, etc.), this sight is a bit narrow-minded.

Finally, a suggestion for a compromise:

How about adding some clause like "ON ERROR DO NOTHING"? Or a "PRAGMA halt_on_error=FALSE"? Or a "DROP ALL VIEWS"? Any of these would help :)