Conditional insertion as a single database transaction in HSQLDB 1.8?

Question

I use a specific database table, such as the "Install" data structure, i.e. you can try to insert the same row several times, but it will only contain one instance. The primary key is the natural key. For example, I want the following series of operations to work fine and result in only one line 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');

Of course, I get an error due to duplication of the 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? That is, only insert if the natural key does not exist yet?

I know I can make a where clause and a subquery to check for the existence of a string first, but it seems to be expensive. These are 2 physical operations for one logical conditional insert operation. Anything like that in SQL?

FYI I am using HSQLDB 1.8

Answer 1

I think I want to use the MERGE command .

However, for this I will have to use HSQLDB 1.9 or later .

Answer 2

EDIT: Just noticed that your question excludes this answer. In any case, the database engine must do the verification somewhere independently to prevail over the unique constraint.

insert ... select where:

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

SQL .

Answer 3

, , ?

... , , , check-then-insert, .

, , ( ), .

Answer 4

SQL . . PL-SQL, , PL-SQL.