IF EXISTS not recognized in Derby

Question

DROP TABLE IF EXISTS Pose ;

results in the error

Error code -1, SQL state 42X01: Syntax error: Encountered "EXISTS" at line 1, column 15.

I'm running this from inside NetBeans 7.3 using the default Derby sample db.

Is this mysql or apache derby? They are not the same thing and don't support exactly the same SQL language. — greg-449, Sep 03 '13 at 16:13
I was unaware! This is apache derby. Does it support some variant of if exists? — Jag, Sep 03 '13 at 17:09

score 8 · Accepted Answer · answered Sep 03 '13 at 17:28

8

Derby does not currently support IF EXISTS

answered Sep 03 '13 at 17:28

greg-449

109,219
232
102
145

score 2 · Answer 2 · edited Nov 06 '18 at 18:29

The MySQL 6.0 syntax for declaring a table is this:

CREATE TABLE [IF NOT EXISTS] tableName ...

and the MySQL syntax for removing a table is this:

DROP TABLE [IF EXISTS] tableName ...

These clauses are MySQL extensions which are not part of the ANSI/ISO SQL Standard. This functionality may be peculiar to MySQL: I can't find anything similar documented for Derby, Postgres, Oracle, or DB2.

score 2 · Answer 3 · answered Aug 08 '16 at 16:20

Are you trying to create a table? If yes, this is what you should do:

 public void createTables() throws SQLException {

    Statement statement = getConnection().createStatement();
    System.out.println("Checking database for table");
    DatabaseMetaData databaseMetadata = getConnection().getMetaData();
    ResultSet resultSet = databaseMetadata.getTables(null, null, "PATIENT", null);
    if (resultSet.next()) {
       System.out.println("TABLE ALREADY EXISTS");
    } else {
        //language=MySQL
        statement.execute("CREATE TABLE Patient (" +
                "CardNumber CHAR(10) NOT NULL PRIMARY KEY, " +
                " FirstName CHAR(50)," +
                " MiddleName CHAR(50)," +
                " LastName CHAR(50) )");
    }
}

Remember to use all caps for the table name you pass into databaseMetadata.getTables(...)

score 1 · Answer 4 · answered Jun 07 '16 at 22:22

The best alternative I can find is to query the system tables to see if the table exists.

select count(*) from sys.systables where tablename = 'YOUR_TABLE_NAME'"

I had a similar issue dropping stored procedures. They can be queried using this statement.

select count(*) from sys.sysaliases where alias = 'YOUR_STORED_PROCEDURE_NAME'

score 0 · Answer 5 · answered Nov 23 '17 at 15:57

If someone is looking to drop and create a table in an sql file that is Run with Spring test framework, Check https://stackoverflow.com/a/47459214/3584693 for an answer that ensures that no exception is thrown when drop table is invoked when said table doesn't exist.

IF EXISTS not recognized in Derby

5 Answers5

Linked