4

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!

3
  • What database platform are you using?
    – mrdenny
    Aug 24, 2011 at 18:45
  • Apologies. I'm using MySQL. Aug 24, 2011 at 18:52
  • Assuming you only want to enforce the uniqueness for ONLY the ones you're converting at a given time, you might do so at the php level (and thus this turns into a nice StackOverflow question). Aug 24, 2011 at 23:06

3 Answers 3

2

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
4
  • Thanks Rolando, but I need to enforce it temporarily on a non-primary/unique field. Basically I will be creating a one-time (hopefully unique) MD5 hash from a string consisting of a combination of the old ID and TITLE. I will then use this as the new TITLE (which I'm pretty sure will have never been used before) and insert the record. I don't actually want to use force TITLE to be unique in future transactions, just for this particular round. I could manually insert and check for each one, but I was hoping for a one-shot deal. Thanks again. Aug 24, 2011 at 20:15
  • 1
    @humble_coder: what's wrong with creating the unique constraint on the column before the run and then dropping it, at the end of all the fun? See Rolando's update for some details.
    – Marian
    Aug 24, 2011 at 21:11
  • @Marian: As I mentioned, the changeover isn't going to occur all at once -- different groups will migrate to the new system (along with their payments/charges) over the next couple of months. I can't afford to force something as arbitrary as TITLEs to be unique for months on end until everyone gets switched over. 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). =\ Aug 24, 2011 at 21:59
  • @humble_Coder: ahh, sorry, missed that part. Then DTest's suggestion should work for you then.
    – Marian
    Aug 25, 2011 at 7:12
1

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;
1
  • Good Thinking !!! That way, no DDL need be applied against the original table. +1 !!! Aug 25, 2011 at 0:44
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.