sqlite and ‘constraint failed’ error while select and insert at the same time

I’m working on migration function. It reads data from old table and inserts it into the new one. All that stuff working in background thread with low priority.

My steps in pseudo code.

sqlite3_prepare_stmt (select statement)
sqlite3_prepare_stmt (insert statement)

while (sqlite3_step (select statement) == SQLITE_ROW)
{
    get data from select row results
    sqlite3_bind select results to insert statement
    sqlite3_step (insert statement)
    sqlite3_reset (insert statement)
}

sqlite3_reset (select statement)

I’m always getting ‘constraint failed’ error on sqlite3_step (insert statement). Why it’s happend and how i could fix that?

UPD: As i’m understand that’s happend because background thread use db handle opened in main thread. Checking that guess now.

UPD2:

sqlite> select sql from sqlite_master where tbl_name = 'tiles';
CREATE TABLE tiles('pk' INTEGER PRIMARY KEY, 'data' BLOB, 'x' INTEGER, 'y' INTEGER, 'z' INTEGER, 'importKey' INTEGER)
sqlite> select sql from sqlite_master where tbl_name = 'tiles_v2';
CREATE TABLE tiles_v2 (pk int primary key, x int, y int, z int, layer int, data blob, timestamp real)

Answer

It probably means your insert statement is violating a constraint in the new table. Could be a primary key constraint, a unique constraint, a foreign key constraint (if you’re using PRAGMA foreign_keys = ON;), and so on.

You fix that either by dropping the constraint, correcting the data, or dropping the data. Dropping the constraint is usually a Bad Thing, but that depends on the application.

Is there a compelling reason to copy data one row at a time instead of as a set?

INSERT INTO new_table
SELECT column_list FROM old_table;

If you need help identifying the constraint, edit your original question, and post the output of these two SQLite queries.

select sql from sqlite_master where tbl_name = 'old_table_name';
select sql from sqlite_master where tbl_name = 'new_table_name';

---

Update: Based on the output of those two queries, I see only one constraint–the primary key constraint in each table. If you haven’t built any triggers on these tables, the only constraint that can fail is the primary key constraint. And the only way that constraint can fail is if you try to insert two rows that have the same value for ‘pk’.

I suppose that could happen in a few different ways.

• The old table has duplicate values in the ‘pk’ column.
• The code that does your migration alters or injects a duplicate value before inserting data into your new table.
• Another process, possibly running on a different computer, is inserting or updating data without your knowledge.
• Other reasons I haven’t thought of yet. 🙂

You can determine whether there are duplicate values of ‘pk’ in the old table by running this query.

select pk 
from old_table_name
group by pk
having count() > 1;

You might consider trying to manually migrate the data using INSERT INTO . . . SELECT . . . If that fails, add a WHERE clause to reduce the size of the set until you isolate the bad data.