Unique index across multiple fields
 

I recently came across this question:
http://stackoverflow.com/questions/6...lumns-in-mysql

I just want to make sure I am understanding the answer correctly. (table names and fields have been made generic to protect the innocent)

If I were to issue the command:
I could then alter my insert statement to include ON DUPLICATE KEY.
From my searching about the ON DUPLICATE KEY clause, I'm finding a lot of options and I'm not quite sure how I would do this. To put it simply, I'm inserting data across 4 fields (field1,field2, field3, field4). I specify the unique index across field2 and field3 so when I insert, if the data being inserted is the same as an existing record on the field2 and field3 columns, I want nothing to be done (ie, don't insert a new record and don't update an existing record).

It looks like my insert command would have to look something like this (note that I insert records via a php form):
Can anyone verify my understanding of this, or perhaps point out flaws in my thinking.

Simpler than that:

INSERT IGNORE ...

That's all you need. Don't need the ON DUPLICATE KEY stuff if you just want to ignore the duplication error.

Once again, thank you. Followup question. Is there a way to have the query let me know which records it did not insert due to being duplicates. Not really important, just more curiosity on my part.

Yes. When an insert succeeds, the number of rows affected is 1. When it doesn't that number is 0.

http://php.net/manual/en/function.my...ected-rows.php

Just had a thought...if I am adding a unique index to a column...am I going to run into problems if the data in that column is identical....for example...I could have multiple entries where the value is "Happy"....I don't care about it being unique in that column, I care about the uniqueness of a new record between the two columns when looked at together. Not sure if I am explaining that correctly...makes sense in my head but then again, I am running on 3 hours of sleep.

Try it. You'll be pleasantly surprised. Or maybe not surprised,but still happy.

Well, I was surprised...not in the happy way.

Ended up getting: #1071 - Specified key was too long; max key length is 1000 bytes.

In looking at my records, there is no way I can modify them at all in order to generate a unique index from them.

Ahhh...bummer. You know, keys *can* be built from partial fields. Could you guaranteed a unique key in that way?

cant you just add a column that is auto increment?

How will that help him detect that the 4 fields he NEEDS to be unique actually are unique? All that would do is ensure that he doesn't duplicate the auto increment column. Kind of useless for his purposes.