Conditional insert as a single database transaction in HSQLDB 1.8?
- by Kevin Pauli
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
