CODEWITHSUNDEEP
sqliteprimary-keyunique-constraint
Julian Drago
Julian Drago

Reputation: 749

SQLite: Prevent insertion of duplicate rows without failing

I've read the question/answer for preventing duplicate rows in a SQLite table that advises placing a constraint on the necessary columns. However, as I understand it, if I'm inserting a number of rows, such as in 

insert into tableA
select *
from tableB

and one of them violates the constraint, the entire insert operation would fail.

Is there a way to avoid duplicates that would allow rows that don't violate the constraint to be inserted, while rows that do violate the constraint aren't?

My "naive" method is to inner join between source data and my destination table based on my columns that need to be unique, and remove the rows from my source data that match, but I'm wondering if there's a way to do this that let's me throw any number of rows at my destination table without having to add an extra step.

Upvotes: 3

Views: 2552

Answers (2)

forpas
forpas

Reputation: 164214

You can do it with INSERT OR IGNORE INTO....:

create table tableA(
  col1 int, 
  col2 text unique
);
insert into tableA(col1, col2) values
(1, 'a'), (2, 'b');

create table tableB(
  col1 int, 
  col2 text unique
);
insert into tableB(col1, col2) values
(1, 'a'), (2, 'b'), (3, 'c');

insert or ignore into tableA(col1, col2) 
select col1, col2 
from tableB;

select * from tableA;

See the demo.
Results:

| col1 | col2 |
| ---- | ---- |
| 1    | a    |
| 2    | b    |
| 3    | c    |

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You may using an INSERT INTO ... SELECT which uses exists logic to check if a duplicate would already exist:

INSERT INTO tableA (col1, col2, col3)
SELECT col1, col2, col3
FROM tableB b
WHERE NOT EXISTS (SELECT 1 FROM tableA a WHERE a.col1 = b.col1 AND a.col2 = b.col2);

This assumes that a "duplicate" is determined by two or more records having the same values for col1 and col2. You may adjust this logic as necessary. The basic idea is that the entire select should run, excluding only those records which you know would cause the constraint to fail.

Upvotes: 0

Related Questions