David,
I've used this, albeit rarely, for tables that get their data from
sloppy sources that include duplicates and near duplicates. If lots
of junk comes in, and I just need one row of the junk for each (you
name the key), this does the trick when yelling at the provider doesn't.
Steve Kass
Drew University
David Portas wrote:
The IGNORE_DUP_KEY option on a unique index defines what happens when you
attempt to insert a row with a duplicate key.
With or without this option duplicate rows are of course not permitted in
unique indexes. Without the IGNORE_DUP_KEY option the entire INSERT will
fail. With IGNORE_DUP_KEY only the duplicated row(s) will fail.
With IGNORE_DUP_KEY, if you insert more than one duplicate row in a single
insert statement then one of them will be inserted (provided a duplicate row
doesn't already exist in the table) and the rest will be ignored. In that
situation you cannot control which row will be inserted and which
duplicate(s) will be ignored.
Because the behaviour of this option is so peculiar and non-standard I
recommend you avoid it. I can't think of a good reason for using this option
at all. If you must use it then do so with care. When writing code that
INSERTs to a table indexed in that way you need to consider the possible
implications for data integrity if only part of your data is added to the
table. In my view it's a lot less trouble to write code that doesn't insert
duplicates in the first place and to do without IGNORE_DUP_KEY.
Finally, a unique *constraint* is normally preferred over a unique *index*
and unique constraints don't allow this option.