Gail,

I ran into this. I don't need help resolving, but I did want to know if you found where the behavior is documented. This is my scenario.

Create table called main with a primary key column called MainID with an identity 1,1 and other columns if you wish. Do not set the pk to clustered index but only as unique.

create a table called SubMain with an FK to PK on main.

Later create a unique clustered index on on MainID call it IDX_UQ_CL_MainID.

Just to test go an delete it. To prove you can delete it.

Then add it back. So it will be there for the next step.

Drop your FK in SubMain

add back your FK in SubMain

Try to delete the IDX_UQ_CL_MainID. You will get the error in this article.

It seems that SQL Server uses the best index at the time an FK is created. Best meaning if there is a unique cluster index on the PK field then use it, if not use the next best index.

Do you know of any documentation or setting that controls this. During the ALTER table ADD FK, you can only specify the column not the index that is used so i see no way to control this.

I can see how to detect it and write better schema change scripts, but not how to prevent it in the first place...Assuming you have to add the indexes in the order specified based on a preexisting schema, or if you just inherit the problem.

Ill work on getting the above in code if you have any trouble reproducing.