Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
What database platform are you using? –  mrdenny Aug 24 '11 at 18:45
    
Apologies. I'm using MySQL. –  humble_coder Aug 24 '11 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). –  Derek Downey Aug 24 '11 at 23:06

3 Answers 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

share|improve this answer

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;
share|improve this answer
    
Good Thinking !!! That way, no DDL need be applied against the original table. +1 !!! –  RolandoMySQLDBA Aug 25 '11 at 0:44

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
share|improve this answer
    
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. –  humble_coder Aug 24 '11 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 '11 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). =\ –  humble_coder Aug 24 '11 at 21:59
    
@humble_Coder: ahh, sorry, missed that part. Then DTest's suggestion should work for you then. –  Marian Aug 25 '11 at 7:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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