title: Compound primary key with autoincrement part
body: <p>I'm using HSQLDB as DB engine. I want to create a table that has compound primary key. I use this code:</p>
  
  <pre><code>CREATE TABLE sample_table ( hash VARCHAR(20), 
  id INTEGER, data VARCHAR(256), PRIMARY KEY(hash, id) )
  </code></pre>
  
  <p>But I need id to be autoincremental. I mean, that when I want to have something like this:</p>
  
  <p>hash id data</p>
  
  <hr>
  
  <p>abc  1  data_string_1
  abc  2  data_string_2
  efg  1  some_string_1
  abc  3  some_data_again
  efg  2  last_string</p>
  
  <p>If I use this code:</p>
  
  <pre><code>CREATE TABLE sample_table ( hash VARCHAR(20), 
  id INTEGER IDENTITY, data VARCHAR(256), PRIMARY KEY(hash, id) )
  </code></pre>
  
  <p>(So I make id an autoincremental primary key) An Exception is thrown:</p>
  
  <blockquote>
    <p>java.sql.SQLSyntaxErrorException: primary key already exist</p>
  </blockquote>
  
  <p>What can I do to solve this problem?
  As for me, using multiple queries with COUNT (id) and then increment this count and then insert new value is not good, I want HSQLDB to do it automatically</p>