How to execute the INSERT only if the record does not exist?

Posted on

Question :

I would like to use something like this in SQlite

insert into table (column) values ('')if not exist;


Answer :

You can use INSERT OR IGNORE .

For this purpose, the column (s) concerned should be declared as UNIQUE .

When a constraint is violated, it is handled according to the conflict clause of constraint declared for the column, which by default is abort .
If it is declared or not, it can be overridden in the INSERT command:

INSERT OR IGNORE INTO table (column) VALUES ('')

So the restriction violation is ignored.

See INSERT and ON CONFLICT clause .


You can use unique to avoid repetition if this is the case.
I found the example below on link

CREATE TABLE employees
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT name_unique UNIQUE (last_name, first_name)

In addition you can also use along with insert to perform validation.
Using the above table, I could do something like this:

 insert into employees(last_name, first_name, hire_date)

    not exists(select 1 from employees where first_name = 'john')

When the query has no results, it will not perform the insert and you can perform any filtering on where .


Leave a Reply

Your email address will not be published. Required fields are marked *