Correct way to do "create only if doesn't already exist" SQL insert?

What is the correct way to create a new record only if it doesn’t already exist, in ODB (2.2.x or 3.0.x)? Specifically, I’m using the OrientJS query function to build a SQL query.

I know that doing a read (to test for the existence of the record) followed by a conditional INSERT (or CREATE VERTEX) can be used, but based on my experience with other databases, I’m cautious that this will not be atomic under high load conditions.

In other SQL database implementations, there are options such as INSERTs that support a WHERE clause or the MSSQL MERGE operation, which allow for a bulletproof atomic way to do this, even under high load. But these options don’t exist in ODB.

Note that this is somewhat the inverse of the very useful (although not in this case) ODB UPSERT operation.

Hi @eric24

if you have a unique index on a property you could use upsert.

See here

https://orientdb.com/docs/last/SQL-Update.html

I do have a unique index, but unless I’m completely missing something, I want the opposite of UPSERT. Specifically, I want to only create the new record if it does not already exist. This wouldn’t be a problem if it weren’t for high-load situations where multiple clients may be trying to create the same record at the same time.

Hi @eric24

you don’t want the update part of the upsert?

Thanls

No, I just don’t think UPSERT is the solution I’m looking for. I use UPSERT a lot, but for when I want to either create or update an existing record. I have the “opposite” problem here that I’m trying to solve. If the record already exists, I want to do nothing. What I really want is an INSERT with a WHERE clause (which is a typical way to do this using something like T-SQL).

@eric24

in that case couldn’t you rely on the unique index and just try to insert a record and handle the exception of duplicate ?