Creating Unique Index

[atiqraza]

I am trying to create a unique index and i want the index to include only distinct values. Here is my query

create unique index POS on POSDAT ([Chain id],Store,Date) with IGNORE_DUP_KEY

I am setting the IGNORE_DUP_KEY to on and it still terminates while creating the index as if it is set to OFF.

Anybody know what i am doing wrong here??

 

[monksnake]

I ran across this, I believe it contains an answer to your question

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/36742;pt=37075

<.

 

[monksnake]

You are trying to create a UNIQUE index on columns that already contain duplicate values. A unique index cannot be created in this case. The IGNORE_DUP_KEY does not ignore duplicate values when creating an index it does as this quote says

If you try to insert a duplicate value into a column that has a unique index, the command is canceled. You can avoid this situation by including the ignore_dup_key option with a unique index.

<.

 

[SQLBill]

Here is a quote from the Books OnLine:

SQL Server does not allow the creation of a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message; duplicates must be eliminated before a unique index can be created on the column(s).

-SQLBill


Posting advice: FAQ481-4875

 

[SQLBill]

I should have added...as monksnake says, IGNORE_DUP_KEY allows inserting duplicate rows AFTER a unique index has been created.

One option you have....create a new table with the unique index using the IGNORE_DUP_KEY option. Copy all the data from the original table into the new table. Drop the original table. Rename the new table.

-SQLBill

Posting advice: FAQ481-4875