title: Conditional insert as a single database transaction in HSQLDB 1.8?
body: <p>I'm using a particular database table like a "Set" data structure, i.e., you can attempt to insert the same row several times, but it will only contain one instance.  The primary key is a natural key.  For example, I want the following series of operations to work fine, and result in only one row for Oklahoma:</p>
  
  <pre><code>insert into states_visited (state_name) values ('Oklahoma');
  insert into states_visited (state_name) values ('Texas');     
  insert into states_visited (state_name) values ('Oklahoma');
  </code></pre>
  
  <p>I am of course getting an error due to the duplicate primary key on subsequent inserts of the same value.  Is there a way to make the insert conditional, so that these errors are not thrown?  I.e. only do the the insert if the natural key does not already exist?</p>
  
  <p>I know I could do a where clause and a subquery to test for the row's existence first, but it seems that would be expensive.  That's 2 physical operations for one logical "conditional insert" operation.  Anything like this in SQL?  </p>
  
  <p>FYI I am using HSQLDB 1.8</p>