SQL Server Performance Forum – Threads Archive

change primary key of a published table

I use a transactional replication and i want to change a primary key of a published table. If i use alter table drop constraint PK_Costumer i get an error: "cannot drop constraint PK_Costumer because table Costumer it is used for replication" . I get the same error if i use sp_droparticle.
Can somebody tell me how can i change a primary key of a published table?

You must unpublish and then re-published the table to alter the PK constaint.

quote:Originally posted by Lazy_DBA You must unpublish and then re-published the table to alter the PK constaint.

I have 200 distibuted agents. If i drop the publication i must recreate those agents.
Do you another method?

Well you didn’t mention that. What version are you running? Hopefully the other members can come up with a work around.

Take help of scripting the replication in case of unpublishing and re-publishing after the constraint correction, BOL is the first hand to help in this matter. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

quote:Originally posted by satya Take help of scripting the replication in case of unpublishing and re-publishing after the constraint correction, BOL is the first hand to help in this matter. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I script the publication. Now i must syncronize the subscription. Thank you for your help.

]]>