How do I insert a record into an sqlite table only if the record does not already exist?

Question

0.00/5 (No votes)

See more:

I apologise if this question is not a good fit for CodeProject, but I have found SQLite questions on here, so here goes...

I am wanting to create an 'atomic' SQL script that will insert a record into a table only if the supplied record's primary key does not already exist, thus avoiding the constraint exception that occurs if the insert goes ahead.

I have the following script which is wrong. I get an error saying the syntax is wrong near the 'WHERE'.

BEGIN EXCLUSIVE TRANSACTION;

INSERT INTO NetworkLocks
(
	PK_id,
	owner_username,
	unique_identifier,
	creation_time
)
VALUES
(
	@ID,
	@owner_username,
	@unique_identifier,
	@creation_time
)
WHERE NOT EXISTS
	( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );

END TRANSACTION;

Any help or advice would be very much appreciated. Also, as a newbie to SQLite, I am finding it difficult to get a handle on how to put together queries. Any pointers to good sources of learning for this would be great.

What I have tried:

I have tried reading all sorts of bits of help and tutorials but still can't hit upon the right syntax.

Posted 20-Dec-17 23:17pm

Patrick Skelton

Updated 21-Dec-17 0:27am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2017-12-21T00:27:00

Solution 1

SQLite Primary Key: The Ultimate Guide To Primary Key[^]:
A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.

Due to the uniqueness, an attempt to insert a row (record) with an already existing primary key will fail. So you can just try inserting and report errors if necessary.

According to your transaction script, PK_id is the only column for the primary key and must be unique therefore. What is the purpose of the unique_identifier column then?

[EDIT]
To solve the error in the SELECT statement use

SQL

( SELECT * FROM NetworkLocks WHERE PK_id = @ID );

You may also specify columns with the table name as NetworkLocks.PK_id.
[/EDIT]

Posted 21-Dec-17 0:27am

Jochen Arndt

Updated 21-Dec-17 1:05am

v2

Comments

Patrick Skelton 21-Dec-17 6:50am

Thank you for the answer.

Currently, my code works as you describe. I rely on the uniqueness of the primary key (PK_id) and catch any exception. This is, however, a foreseeable error. It happens in normal operation. I'm not fond of exceptions for this situation. I was hoping to change the SQL script so that it always runs to completion but somehow returns a result. Perhaps the number of rows affected might be zero instead of one?

The Guid is difficult to explain. It is possible (though unlikely) for locks in this table to become orphaned, perhaps as a result of a power cut for example. The Guid simply adds an extra layer of protection, allowing the calling thread or application instance to know beyond any doubt whether or not it is the owner of any given lock record.

Jochen Arndt 21-Dec-17 7:17am

You have not supplied information from where you are calling this but "catch any exception" implies some kind of programming language.

The usual method would be catching exceptions because then you can rely on the thread safeness of the database engine. If you check for existance of a record first you may have race conditions (which should not care because insertion fails but throws an exception again).

I'm not sure if this can occur also with such transaction scripts. But even with the script you have an additional query that is performed anyway internally by the INSERT.

Patrick Skelton 21-Dec-17 7:29am

You are correct - this is called via a C#.net .DLL API to SQLite.

I guess I'll have to live with the exception approach. I can reduce the chances of the exception occurring virtually to zero by doing a quick check in C# code before I attempt the insert. I realise this is not thread-safe, but, as you say, the worst that can happen is that the subsequent insert throws an exception, which my code then handles okay.

I am surprised that my seemingly simple requirement is not easier to implement though.

Jochen Arndt 21-Dec-17 7:50am

You need catching exceptions anyway to be prepared for other errors too (especially during development where you may have syntax errors or typos in table or column names).

The only solution I can think of is checking if the API provides also functions that does not throw exceptions but return a state instead.