DROP [IF EXISTS] ... [CORE3863]

[firebird-automations]

Submitted by: eXandr (i.reg)

Votes: 4

It should be possible to determine not cause an error when deleting an object that does not exist
Something like that:

execute statement 'ALTER TABLE SOME_TABLE DROP [IF EXISTS] SOME_FIELD'; -- no raise error if field SOME_FIELD is not exist
or
execute statement 'DROP INDEX IDX1 [IF EXISTS]'; -- no raise error if index IDX1 is not exist

Workaround for now is:

begin
execute statement 'ALTER TABLE SOME_TABLE DROP SOME_FIELD';
when any do
begin
end
end

But this solution hides any error that is very bad. Another solution is to access the system tables, but it requires more code and can add even more errors.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

There are techniques (queries against system tables) which can allow you to test for the existance of the objects before you delete them.

So, I would oppose the project introducing non-standard SQL syntax for this case.

[firebird-automations]

Commented by: eXandr (i.reg)

Sean Leyne, I know that I can use system tables and I know that my suggestion is not the part of standard of SQL, but this feature may reduce count lines of code, count of errors and can improve code readability.

[ertankucukoglu]

Even if it is not in standard such use case is supported by several database systems and readability is far more easy compared to existing method (execute statement). I would also ask CREATE / ADD statements and allowing NOT usage as well.

CREATE TABLE IF NOT EXISTS table_name
ALTER TABLE ADD COLUMN IF NOT EXISTS column_name
CREATE INDEX IF NOT EXISTS index_name

[asfernandes]

I think this is like RECREATE or CREATE OR ALTER. It's not standard, but if it helps users, why not?
But I would go only for the top level objects, not for columns like in your above example.

[dyemanov]

I think this is like RECREATE or CREATE OR ALTER. It's not standard, but if it helps users, why not? But I would go only for the top level objects, not for columns like in your above example.

Agreed.

[ertankucukoglu]

I do not think it is like RECREATE. I do not want to create it again or modify existing table data. I work on database update scripts quite a lot. Tables are already there 99% of the time. Adding columns is one of the main tasks I frequently do and so I gave an example of it. Dmitry Yemanov ***@***.***>, 10 Haz 2023 Cmt, 22:02 tarihinde şunu yazdı:

…

I think this is like RECREATE or CREATE OR ALTER. It's not standard, but if it helps users, why not? But I would go only for the top level objects, not for columns like in your above example. Agreed. — Reply to this email directly, view it on GitHub <#4203 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADQ6UECKOYIYYXK5BJAR2SLXKTAF3ANCNFSM6AAAAAAZBGZHHI> . You are receiving this because you commented.Message ID: ***@***.***>

[aafemt]

In your own database, it is much easier and more predictable to know exactly which tables exist and which aren't. If the database structure is fixed, documented and changed only on new release - there is no need to check existence of database objects. So it is just a matter of developer's discipline.

[ertankucukoglu]

It is not my database, I am a freelancer mostly fixing other's mess. Dimitry Sibiryakov ***@***.***>, 10 Haz 2023 Cmt, 22:38 tarihinde şunu yazdı:

…

In your own database, it is much easier and more predictable to know exactly which tables exist and which aren't. If the database structure is fixed, documented and changed only on new release - there is no need to check existence of database objects. So it is just a matter of developer's discipline. — Reply to this email directly, view it on GitHub <#4203 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADQ6UECZ25AY2KJO4JMMC7LXKTEMRANCNFSM6AAAAAAZBGZHHI> . You are receiving this because you commented.Message ID: ***@***.***>

[mrotteveel]

I do not think it is like RECREATE. I do not want to create it again or modify existing table data.

That is not what Adriano said. He just said the RECREATE and CREATE OR ALTER statements are also non-standard, but we introduced them because they can be helpful. Similar reasoning can be applied for adding IF NOT EXISTS for CREATE.

[mrotteveel]

If this is going to get added, I'd also like its counter part IF EXISTS to DROP <object-type>, e.g DROP TABLE IF EXISTS <table-name>.

[dyemanov]

I'm somewhat worried about the CREATE part of this feature request. Imagine a case when the object exists but declared differently. No error is raised with CREATE ... IF NOT EXISTS but the object keeps its old definition which is maybe not what you'd expect. CREATE OR ALTER is IMHO superior in this regard. This problem does not exist for DROP ... IF EXISTS.

[mrotteveel]

I'm somewhat worried about the CREATE part of this feature request. Imagine a case when the object exists but declared differently. No error is raised with CREATE ... IF NOT EXISTS but the object keeps its old definition which is maybe not what you'd expect. CREATE OR ALTER is IMHO superior in this regard. This problem does not exist for DROP ... IF EXISTS.

As far as I'm aware, all other DBMSes that implement IF NOT EXISTS are simply going by existence of an object with that name, they don't care about the actual content or structure of that object. It would be the responsibility of the one writing the script to make "the right choice".