sp1 messup deleting PK?

Microsoft SQL Server

Just saw something weird, I think it's OK now, but for a moment it looked
like something major was hosed.

Was trying out a script on my workstation copy of 2005sp1, trying to drop a
nonclustered PK and a clustered index on the same field. I didn't realize it
would resist doing this, when there are a dozen FK's using it! And, I guess
you have to use alter table to drop a PK constraint. Anyway, after error (at
just which stage I'm not sure), the SSMS modify applet still showed the PK
key on the original field, but the object explorer showed the PK index gone,
it showed another field as clustered PK (?), that was not even unique. Hello?

I exited SSMS and restarted, same results. (I did not bounce the service or
the workstation)

I let the modify applet create a script for me to drop all FKs and ran that,
then both the modify applet and the object explorer showed no PK.

Then I manually dropped each remaining index - the object explorer did not
even offer me a delete option on any of them! Then I added the PK back as a
clustered constraint, seems happy now.

But, ... what kind of madness did I just escape, or will it come back and
still bite me?

Josh
Hi

Hopefully this is not a production system as doing ad-hoc changes is not
something to be advised! A scripted solution could have been tested
elsewhere and also stored in the change tracking system.

Certainly with Enterprise Manager I didn't always believe what it told me
especially where permissions was concerned, so it may well be a display
issue with SSMS. Do you have a script and instructions to reproduce this
reliably?

John
As I said, this was on my workstation, trying out a script.



I guess I know how to set it up again, as described, but I'm not eager
to do so. Probably take a couple of hours to diagnose, and hey,
that's on an SP1 box so I'm sure Microsoft would seriously not care.
Well, it's posted below, if anyone (eg, at Microsoft) is more
ambitious than me.

Josh