title: Spring test with H2 in memory database truncate all tables
body: <p>I'm a junior CS major student working on an MVC project using spring and I am quite new to spring. </p>
  
  <p>I set up an H2 in memory db and wrote 2 sql scripts; one to populate the db and another one to flush it empty. </p>
  
  <p>As far as I know, the practical difference between <code>truncate table &lt;table name&gt;</code> and <code>delete from &lt;table name&gt;</code> is truncate table is ddl thus its faster and it also resets the auto incremented columns. The problem is H2's truncate table does not reset auto incremented columns. Could you point out where I'm messing up? </p>
  
  <p>I'm using spring's SqlGroup annotation to run the scripts. </p>
  
  <pre class="lang-java prettyprint-override"><code>@SqlGroup({
      @Sql(executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD, scripts = 
                                                  "classpath:populate.sql"),
      @Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts = 
                                                    "classpath:cleanup.sql")
  })
  </code></pre>
  
  <p>Here's the cleanup.sql that is supposed to truncate every table.</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET FOREIGN_KEY_CHECKS=0;
  
  TRUNCATE TABLE `criteria`;
  TRUNCATE TABLE `job_title`;
  TRUNCATE TABLE `organization`;
  TRUNCATE TABLE `organization_teams`;
  TRUNCATE TABLE `organization_users`;
  TRUNCATE TABLE `organization_job_titles`;
  TRUNCATE TABLE `review`;
  TRUNCATE TABLE `review_evaluation`;
  TRUNCATE TABLE `team`;
  TRUNCATE TABLE `team_members`;
  TRUNCATE TABLE `user`;
  TRUNCATE TABLE `user_criteria_list`;
  
  SET FOREIGN_KEY_CHECKS=1;
  </code></pre>
  
  <p>And populate.sql simply inserts a bunch of rows to those tables without violating integrity constraints.</p>
  
  <p>So when i run my test class, only the first method passes. For the rest i get stuff like this:</p>
  
  <p>Referential integrity constraint violation: "FK3MAB5XYC980PSHDJ3JJ6XNMMT: PUBLIC.ORGANIZATION_JOB_TITLES FOREIGN KEY(JOB_TITLES_ID) REFERENCES PUBLIC.JOB_TITLE(ID) (1)"; SQL statement:
  INSERT INTO <code>organization_job_titles</code>(organization_id, job_titles_id) VALUES(1, 1)  </p>
  
  <p>I thought the problem arose from auto incremented columns not being reset. So i written a different test class:</p>
  
  <p>monAutoIncTest.java</p>
  
  <pre class="lang-java prettyprint-override"><code> @Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts =
      "classpath:truncateTable.sql")
      public class monAutoIncTest {
          @Autowired
          OrganizationService organizationService;
  
          @Test
              public void h2truncate_pls() throws BaseException {
              organizationService.add(new Organization("Doogle"));
              Organization fetched = organizationService.getByName("Doogle");
              Assert.assertEquals(1, fetched.getId());
          }
  
          @Test
          public void h2truncate_plz() throws BaseException {
              organizationService.add(new Organization("Zoogle"));
              Organization fetched = organizationService.getByName("Zoogle");
              Assert.assertEquals(1, fetched.getId());
          }
      } 
  </code></pre>
  
  <p>With truncateTable.sql </p>
  
  <pre class="lang-sql prettyprint-override"><code>SET FOREIGN_KEY_CHECKS=0;
  
  TRUNCATE TABLE `organization`;
  
  SET FOREIGN_KEY_CHECKS=1;
  </code></pre>
  
  <p>When the test runs, whichever method that is run first passes, and the other gives me this.</p>
  
  <p>java.lang.AssertionError: 
  Expected :1
  Actual   :2</p>