Conditional insert as a single database transaction in HSQLDB 1.8?

Posted by Kevin Pauli on Stack Overflow See other posts from Stack Overflow or by Kevin Pauli
Published on 2010-03-03T20:43:18Z Indexed on 2010/03/13 10:05 UTC
Read the original article Hit count: 249

Filed under:
|

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:

insert into states_visited (state_name) values ('Oklahoma');
insert into states_visited (state_name) values ('Texas');     
insert into states_visited (state_name) values ('Oklahoma');

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?

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?

FYI I am using HSQLDB 1.8

© Stack Overflow or respective owner

Related posts about sql

Related posts about hsqldb