I'm developing an application where users will be importing a few thousand records from a text file. I have a unique constraint on 3 of the columns in my table, but when I attempt to import duplicate records I receive this error.
Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2013-06-01 15:25:41-2013-06-01 15:25:42-null' for key 'start_time'
It looks like CakePHP will stop attempting to insert data once an insert fails due to a constraint violation. Is there any way to simply have CakePHP ignore the constraint violation?
Thank you for your time.
The problem is we are importing thousands of records at a time. Checking if a row exists before attempting to insert(I believe this is how CakePHP's unique validation works) will double the amount of queries if I were to try to save it row by row. I'm going to remove the unique constraint on the column and just insert all of the rows. After the new rows are inserted, I'm going to add a unique constraint to those columns and then remove the constraint. I think this will work well in my case because we plan on importing new records only once a month.
It really depends on how your are importing your data and what RDBMS you are using.
If you are looping line-by-line over the text file and inserting data after each line, you could catch the exception and move on to the next line of your text file. Just remember to push the failed row into some kind of error log, so you'll be able to find which inserts failed. The bigger issue I see is that a thrown Exception might ruin your current transaction, and for mass data insertion you'll definitely want to wrap everything up in a singular transaction for best performance.
If you are using MySQL and CSV files, there's a LOAD DATA INFILE command you could explore using.