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

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.

Just in case anyone lands here looking for "Constraint failed" error message, make sure your Id column's type is INTEGER, not INTEGER (0, 15) or something.

Background

If your table has a column named Id, with type INTEGER and set as Primary Key, SQLite treats it as an alias for the built-in column RowId. This column works like an auto-increment column. In my case, this column was working fine till some table designer (probably the one created by SQLite guys for Visual Studio) changed column type from INTEGER to INTEGER (0, 15) and all of a sudden my application started throwing Constraint failed exception.