Tuesday, February 12, 2013

SQLite INSERT OR REPLACE without a constraint

I learned something new yesterday while working on a simple four column table that keeps a list of the favorite tools in NetScanTools Pro. This table was set up without a constraint meaning there is nothing stopping us from inserting a row that is identical to a current row. Here is the DDL for the table:

CREATE TABLE [favorites] (
[col1] NUMBER,
[col2] TEXT,
[col3] NUMBER,
[col4] NUMBER);

As you can see - no constraints.

While working with the right click menu that adds a tool to NetScanTools Pro I discovered that it was adding the same row twice. I was using an INSERT INTO favorites... SQL command. So I thought I would get rid of the duplicates by using INSERT OR REPLACE INTO favorites. Wrong.

What I didn't know/remember was that the OR REPLACE means to replace the rows that are causing a constraint violation. Since there is no constraint, there is no constraint violation meaning the OR REPLACE clause does nothing and multiple identical rows can still be inserted.

An obvious solution is to change the table and add a constraint like this:

CREATE TABLE [favorites] (
[col1] NUMBER,
[col2] TEXT,
[col3] NUMBER,
[col4] NUMBER,

But to do so in an installed base of software in the hands of customers would require changes: ie. copying the existing table, deleting the old table and recreating it with the constraint. Plus it would require checking every time the program is started to make sure it had been done or some other versioning method would have to be used. Lots of details.

I arrived at a different solution using a TRIGGER to remove duplicate rows before the INSERT happens. This requires no changes to the design of the table and does not require changing anything in the user's database. This statement creates a TEMPORARY TRIGGER if it does not already exist and does this before an INSERT happens. It deletes any rows that match 'new' col1, then does the INSERT.

ON favorites
DELETE FROM favorites WHERE new.col1= col1;

Pretty simple and appears to get the job done without modifying the original table. TRIGGERS are very powerful and I will probably be using more of them in the future - but using them with care.

Thanks to Bogdan Ureche for help in understanding what was going on with INSERT OR REPLACE without a corresponding CONSTRAINT.


No comments: