Conditional insert as a single database transaction in HSQLDB 1.8?

Question

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

Answer 1

if you don't really care about dupes being swallowed, then can't you just go ahead and try to insert anyway, and just catch the exception?

... bear in mind that this code is being called a lot, this maybe less performant than the check-then-insert method you describe above.

finally, you could always do the first suggestion but keep a cache in memory of the previously seen values (in that session) which would limit the performance downside of doing the exception handling.

Answer 2

EDIT: Just noticed your question excludes this answer. In any case, the database engine must do the where check regardless, to enfore the the unique constraint.

The insert ... select statement allows a where clause:

insert into states_visited (state_name) 
select 'Oklahoma'
where not exists
(
    select  * 
    from    states_visited
    where   state_name = 'Oklahoma'
)

Most SQL databases will execute a single statement in a transactional manner.

Answer 3

There is no way to do this in pure SQL. You need a procedural language for the task. In PL-SQL for instance you can use an anonymous PL-SQL block.

Answer 4

I think I'll want to use the MERGE command.

However to do so I'll have to use HSQLDB 1.9 or later.