Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--