title: SQL JDBC template drop table issue: drop table with FK and Unique index (JDBC template)
body: <p>I am using JDBC template for my web+MVC+Security (using Spring 2.5, Spring security 2.0.4) for the user authentication with roles in it. When I am trying to execute it, its giving me the following error:</p>
  
  <pre><code>Mar 06, 2012 10:10:42 AM org.apache.catalina.core.ApplicationContext log
  INFO: Closing Spring root WebApplicationContext
  Mar 06, 2012 10:11:42 AM org.apache.catalina.core.ApplicationContext log
  INFO: Initializing Spring root WebApplicationContext
  Mar 06, 2012 10:11:43 AM org.apache.catalina.core.StandardContext listenerStart
  SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
  org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourcePopulator' defined in ServletContext resource [/WEB-INF/dataAccessContext.xml]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,ENABLED BOOLEAN NOT NULL);]; nested exception is java.sql.SQLException: Table already exists: USERS in statement [CREATE TABLE USERS]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
      at java.security.AccessController.doPrivileged(Native Method)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
      at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
      at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
      at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
      at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429)
      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
      at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255)
      at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199)
      at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
      at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4135)
      at org.apache.catalina.core.StandardContext.start(StandardContext.java:4630)
      at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
      at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
      at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546)
      at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905)
      at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740)
      at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500)
      at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1345)
      at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:303)
      at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
      at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1337)
      at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1601)
      at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1610)
      at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1590)
      at java.lang.Thread.run(Unknown Source)
  Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,ENABLED BOOLEAN NOT NULL);]; nested exception is java.sql.SQLException: Table already exists: USERS in statement [CREATE TABLE USERS]
      at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429)
      at springapp1.service.HsqldbSchemaAndDataPopulator.afterPropertiesSet(HsqldbSchemaAndDataPopulator.java:23)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335)
      ... 31 more
  Caused by: java.sql.SQLException: Table already exists: USERS in statement [CREATE TABLE USERS]
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
      at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source)
      at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
      ... 35 more
  </code></pre>
  
  <p>HsqldbSchemaAndDataPopulator.java (JDBC template)</p>
  
  <pre><code>public class HsqldbSchemaAndDataPopulator implements InitializingBean {
  
      private JdbcTemplate template;
  
      public void afterPropertiesSet() throws Exception {
          Assert.notNull(template, "dataSource required");
  
          // add tables to represent admin core-domain instances.
          template
                  .execute("CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,"
                          + "PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,"
                          + "ENABLED BOOLEAN NOT NULL);");
          template
                  .execute("CREATE TABLE AUTHORITIES(USERNAME VARCHAR_IGNORECASE(50) NOT NULL,AUTHORITY VARCHAR_IGNORECASE(50) NOT NULL,CONSTRAINT FK_AUTHORITIES_USERS FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME));");
          template
                  .execute("CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY);");
  
          // add tables to represent bug tracking domain instances.
          template
                  .execute("CREATE TABLE PROJECTS(ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR_IGNORECASE(50) NOT NULL, DESCRIPTION VARCHAR_IGNORECASE(200) NOT NULL);");
          // insert data here
          template
                  .execute("INSERT INTO USERS VALUES('disabled','disabled',FALSE);");
          template.execute("INSERT INTO USERS VALUES('admin','admin',TRUE);");
  
          template
                  .execute("INSERT INTO USERS VALUES('username','password',TRUE);");
          template.execute("INSERT INTO USERS VALUES('user','pass',TRUE);");
  
      //ADMIN-ROLES
  
          template
                  .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_USER');");
  
          template
                  .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_ADMIN');");
      //USER-ROLES
          template
                  .execute("INSERT INTO AUTHORITIES VALUES('username','ROLE_USER');");
   }
  
      public void setDataSource(final DataSource dataSource) {
          this.template = new JdbcTemplate(dataSource);
      }
  }
  </code></pre>
  
  <p>So, I add the following code to the HsqldbSchemaAndDataPopulator class:</p>
  
  <pre><code>//Delete tables if exist
      template
      .execute("ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS;");
      template
      .execute("ALTER TABLE AUTHORITIES DROP INDEX IX_AUTH_USERNAME;");
      template
      .execute("DROP TABLE PROJECTS IF EXISTS;");
      template
      .execute("DROP TABLE USERS IF EXISTS;");
      template
      .execute("DROP TABLE USERS IF AUTHORITIES;");
  </code></pre>
  
  <p>Now it gives me the following error:</p>
  
  <pre><code>Mar 06, 2012 10:11:43 AM org.apache.catalina.core.ApplicationContext log
  INFO: Closing Spring root WebApplicationContext
  Mar 06, 2012 10:16:33 AM org.apache.catalina.core.ApplicationContext log
  INFO: Initializing Spring root WebApplicationContext
  Mar 06, 2012 10:16:34 AM org.apache.catalina.core.StandardContext listenerStart
  SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
  org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourcePopulator' defined in ServletContext resource [/WEB-INF/dataAccessContext.xml]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS;]; nested exception is java.sql.SQLException: Constraint not found FK_AUTHORITIES_USERS in table: AUTHORITIES in statement [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
      at java.security.AccessController.doPrivileged(Native Method)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
      at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
      at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
      at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
      at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429)
      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
      at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255)
      at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199)
      at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
      at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4135)
      at org.apache.catalina.core.StandardContext.start(StandardContext.java:4630)
      at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
      at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
      at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546)
      at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1041)
      at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:964)
      at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:502)
      at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1345)
      at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:303)
      at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
      at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1337)
      at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1601)
      at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1610)
      at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1590)
      at java.lang.Thread.run(Unknown Source)
  Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS;]; nested exception is java.sql.SQLException: Constraint not found FK_AUTHORITIES_USERS in table: AUTHORITIES in statement [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS]
      at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:429)
      at springapp1.service.HsqldbSchemaAndDataPopulator.afterPropertiesSet(HsqldbSchemaAndDataPopulator.java:23)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335)
      ... 31 more
  Caused by: java.sql.SQLException: Constraint not found FK_AUTHORITIES_USERS in table: AUTHORITIES in statement [ALTER TABLE AUTHORITIES DROP CONSTRAINT FK_AUTHORITIES_USERS]
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
      at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source)
      at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
      ... 35 more
  </code></pre>
  
  <p>And this is my dataAccessContext.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.springframework.org/schema/beans
         http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"&gt;
  
      &lt;!-- business stuff below --&gt;
      &lt;bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
          &lt;property name="driverClassName" value="org.hsqldb.jdbcDriver" /&gt;
          &lt;property name="url" value="jdbc:hsqldb:hsql://localhost" /&gt;
          &lt;property name="username" value="sa" /&gt;
          &lt;property name="password" value="" /&gt;
      &lt;/bean&gt;
  
      &lt;bean id="dataSourcePopulator" class="springapp1.service.HsqldbSchemaAndDataPopulator"&gt;
          &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;/bean&gt;
  &lt;/beans&gt;
  </code></pre>
  
  <p>I think I am doing something wrong with the SQL statements, but not exactly sure what it is? Can someone help me with this one? thank you.</p>