Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?

Question

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted? For instance, I'm splitting payments and charges to their own tables from a previously unified "transactions" table. Of course, the new tables have their own autoincremented ids and won't be cueing off the previously existing "transaction" ids so I was going to create a unique key for each transaction (based on an MD5 of the previous transaction_id and transaction_title) and use it as the new title. Basically the pseudocode of the PHP/SQL would go something like this:

$old_payments = getOldPayments();  //DON'T WORRY ABOUT THIS, IT WORKS

foreach($old_payments as $payment){
    $unique_md5_info = md5($payment['transaction_id']."_".$payment['transaction_title']);
    $query = sprintf("INSERT INTO payments (title, amount, due_date) VALUES('%s','%s','%s') WHERE title <> '%s'",$unique_md5_info,$payment['amount'],$payment['due_date'],$unique_md5_info);
}

Obviously, in a perfect world we'd only need to run this once and be done. However there are groups on multiple servers that won't be moving over at the same time so, in order to accommodate the staggered move, we'll have to run the query on different dates while being sure not to import any duplicate transactions.

Anyway, I'm probably seriously overthinking this issue, but hopefully someone has a reasonable solution.

Best!

Answer 1

You could use INSERT IGNORE. This would reject any INSERTs where the primary key or unique key already exists.

If you have to update non-keyed columns in a table in the event that the primary key or unique already exists, you can use INSERT ... ON DUPLICATE KEY UPDATE. I demonstrated how to use this in another question in the DBA StackExchange.

For your particular query, please make sure the title column is either the primary key or has a unique key defined. Then, INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE will work for you.

Give it a Try !!!

UPDATE

If the payments table is not too big, try this crazy ideas

Create a UNIQUE INDEX on this one-off

ALTER TABLE payments ADD UNIQUE INDEX title_amount_due_date (title, amount, due_date);

Perform your INSERTs IGNORE from the multiple servers. When done...

ALTER TABLE payments DROP INDEX title_amount_due_date

Answer 2

You could create a second table with the UNIQUE INDEX mentioned by @Rolando. This would be the table you run inserts from your conversion script and using INDEX IGNORE.

Normal day to day operations would go to the real table which does not have a unique index.

As part of your conversion script (if you need the data immediately in the real table), at the end you would insert only the rows that have that unique title. You leave the data in the temporary table until, months later, you're finally finished with all the old transactions.

Potential query (untested):

INSERT INTO realTable SELECT tempTable.* FROM tempTable 
 LEFT JOIN realTable ON realTable.title=tempTable.title 
 WHERE realTable.id IS NULL;

Answer 3

from comments:

I suppose I could add an extra field for unique identification, but I that still leaves a problem for non import-related items (day to day use of the system).

If I understand correctly (and I'm not sure I do), you can add the extra field but use nulls for the "non import-related items" - the unique constraint will then effectively ignore the rows with a null but enforce uniqueness for the others