Forum Stats

  • 3,826,626 Users
  • 2,260,682 Discussions
  • 7,897,041 Comments

Discussions

drop table if exists

Martin Preiss
Martin Preiss Member Posts: 2,381 Gold Trophy
edited May 7, 2020 6:06PM in Database Ideas - Ideas

Some other RDBMS - for example MySQL and postgreSQL: PostgreSQL: Documentation: 9.4: DROP TABLE - support a "drop table if exists" command that avoids an error if you try to drop a table that does actually not exists. I think that's sometimes useful.

Martin PreissctriebBPeaslandDBAManish ChaturvediLothar FlatzJagadekaraChris HuntGeert GruwezKayKJ.SchnackenbergFranck Pachotvinaykumar2Sven W.ulohmannApexBinetrentN.B.sensoftLukas Ederdherzhauyasuo.hondaUser_JDNQ2Jeffrey KempRakeshDTony Andrewspnoskofac586Gerald Venzl-OracleMathias MagnussonMr. Ccormaco3011615Emad Al-Mousa3881718Racer I.Chase MarlerDirk.NachbarPeter Hraškorober584812User_9GEWMraviraushantvCaUser_930JVJeff Kemp-OracleUser_G3UV3User_DU2XKandre.psantos
62 votes

Under Review - Voting Still Open · Last Updated

«134

Comments

  • ctrieb
    ctrieb Member Posts: 314 Gold Trophy

    A nice idea, but not really necessary. You can write a simple error routine to solve this problem in actual ORACLE DB Version.

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    A nice idea, but not really necessary. You can write a simple error routine to solve this problem in actual ORACLE DB Version.

    yes, "not really necessary" is the fitting category - but on the other hand it should be quite cheap to implement.

    ApexBine3011615
  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy

    not required as it errors out

  • abhinivesh.jain
    abhinivesh.jain Member Posts: 307 Blue Ribbon

    Getting error is more logical. Table or view doesn't exist error comes even when someone tries to select from non-existent table so it absolutely makes sense to get the same error when someone tries to drop it.

    Thanks,

    Abhi

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Isn't this what BEGIN/EXCEPTION is for?

    Rather than get an error, you just go to the exception, and handle the missing object there.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jun 21, 2016 6:46AM

    Isn't this what BEGIN/EXCEPTION is for?

    Rather than get an error, you just go to the exception, and handle the missing object there.

    Not really. BEGIN / EXCEPTION is plsql or at least an anonymous block in SQL.

    DROP TABLE is sql. It is not allowed to write this inside a plsql block. So exception handling for that is really tricky.

    The usual way to solve that is to switch SQL*PLUS error handling off before the drop and on after the drop. Which is a nuisance.

    typical example from certain installation scripts:

    whenever sqlerror continue;

    drop table abc;

    whenever sqlerror exit failure;

    create table abc ...

    @CTrieb: Do you have another simple way how to solve that?

    ApexBine
  • pnosko
    pnosko Member Posts: 284 Bronze Badge

    It would greatly simplify writing installation scripts.

    Lukas EderPeter Hraško
  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    A nice idea, but not really necessary. You can write a simple error routine to solve this problem in actual ORACLE DB Version.

    That workaround is really a pain if you're writing large migration scripts that might run several times depending on how you deploy things... Many things aren't really necessary. For instance, AVG(x) is not necessary because it can be calculated as SUM(x) / COUNT(*), but it's still super useful, no?

  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    not required as it errors out

    I get really anxious when my complex installation scripts have errors in them. I'd like to have no error at all or meaningful errors, no errors I can "safely ignore" (after manual and tedious introspection)

  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    A few databases already support such syntax, including PostgreSQL and MySQL. Note that this isn't limited to only DROP TABLE statements, but it could work with pretty much any DDL statement:

    • DROP <object> [ IF EXISTS ]
    • ALTER <object> [ IF EXISTS ] ...
    • ALTER <table> [ IF EXISTS ] ADD <column> [ IF NOT EXISTS ]
    • ALTER <table> [ IF EXISTS ] DROP <column> [ IF EXISTS ]
    • CREATE <object> [ IF NOT EXISTS ]

    I would find this really really useful!