Effects of dropping PK on other indexes

I need to update a large number of keys in a large SQL Server 2005 database and will be dropping FKs and PKs on a bunch of tables, doing the update (which replaces the values of the PK/FK) and then adding the FK and PK again.

My questions are:

1. Will this process have any effect on exsiting indexes that exist on those tables, either indexes that include the PK/FK fields or indexes on other unaffected fields. ie will all indexes still exi开发者_JAVA技巧sts, will they need a rebuild?

2. Will this process affect table statistics, requiring a recalc?

Many thanks

---

If you drop a PK (which is usually a clustered index) SQL Server will drop and recreate all non clustered indexes(this is needed because if you have a clustered index the non clustered indexes point to the clustered index). If you don't have a clustered index (a heap) the non clustered indexes point to the data row

rebuilding a clustered index will automatically update statistics, a reorg won't

If you created the keys with cascade update then they should be updated automatically

example

create table pri(id int not null primary key)
go

create table ForeignK(fid int not null)
go


ALTER TABLE dbo.ForeignK ADD CONSTRAINT
    FK_ForeignK_pri FOREIGN KEY
    (fid) REFERENCES dbo.pri(id) ON UPDATE  CASCADE 
     ON DELETE  NO ACTION 

     insert pri values(1)
     insert ForeignK values(1)

now update the PK table

 update pri set id = 5
 go

this will now be 5 also

 select * from ForeignK

---

Every change in indexed column has a propagation in structure change.

For foreign key you could disable them and then rebuild. For private key only thing you can do is to rebuild them. I think that SQLMenace explained it clearly why.

More