January 2, 2003 at 11:55 am
Hello,
I have an Unique Non-Clustered index using fields called 'Permnum', and 'TestShortName' on a table called 'tblMMStudentTestScores'. I need to alter this index to add 'Ignore_Dup_Key' to help me with some error handling.
I attempted to change the index in two ways. First, I tried this:
*******************************
Create Unique Nonclustered
Index UN_tblMMStudentTestScores ON tblMMStudentTestScores (Permnum,TestShortName)
with
Ignore_Dup_Key,
Drop_Existing
******************************
Upon trying the above code, I received the following error message:
"Server: Msg 1907, Level 16, State 1, Line 4
Cannot re-create index 'UN_tblMMStudentTestScores'. The new index definition does not match the constraint being enforced by the existing index."
Then I tried to simply drop the index as follows:
*******************
Drop index tblMMStudentTestScores.UN_tblMMStudentTestScores
*******************
Upon trying this method, I received the following error message:
"Server: Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'tblMMStudentTestScores.UN_tblMMStudentTestScores'. It is being used for UNIQUE KEY constraint enforcement."
How can I alter/drop this index to add 'Ignore_Dup_Key'? Thanks.
CSDunn
January 2, 2003 at 3:58 pm
From what you show, I would say that the index is also a Primary Key constraint. You would have to remove this constraint in order to change the index to be non-unique.
Just as a side thought, but are you sure that's what you want to do if it IS a primary key constraint? Are you re-defining the unique identifier for that table?
January 2, 2003 at 5:03 pm
Use statement
ALTER TABLE tblMMStudentTestScores
DROP CONSTRAINT UN_tblMMStudentTestScores
or
ALTER TABLE tblMMStudentTestScores
ALTER COLUMN ...
- something like that to get rid of the constraint. It should help.
Edited by - mromm on 01/02/2003 5:04:25 PM
January 2, 2003 at 5:29 pm
Actually, I had a unique index and a unique constraint set up on the same fields. I dropped the constraint, then added a new unique nonclustered index with Ignore_Dup_Key, and now I am getting the results I expected. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply