>Some time ago I set up an alternate key (unique key) index (named AK_CompReference2). Now I want to drop it but when I try to Delete it in the SSMS I get error that "the index cannot be dropped because it is being used in UNIQUE KEY constraint enforcement" But what if I don't want this enforcement any more? How can I drop this index?
You may want to first drop unique constraint.
Check
http://msdn.microsoft.com/en-us/library/ms177420.aspx
If it's not broken, fix it until it is.
My Blog