none
Need to add a column to a composite primary key on a replicated table RRS feed

  • Question

  • I currently have a table with a 3 column composite PK, due to business reasons I need to add another column to that PK. However the table is replicated and I am therefore unable to just simply add it to the PK without it giving me an error.

    Is there a way to do what I need to do without taking the table out of replication, dropping the table, recreating the table and adding the table back into replication?

    Thank you for any assistance.

    Wayne E. Pfeffer

    Monday, June 15, 2020 6:59 PM

All replies

  • Sadly there is no way of doing this.
    Monday, June 15, 2020 7:56 PM
    Moderator
  • So what is the preferred method of doing this. Do I need to shut down replication completely or do I just need to know which publication(s) the table is in so I can remove it from replication, do what I need to do, and then put it back?
    Monday, June 15, 2020 8:22 PM
  • drop the article from the subscription(s), drop the article from the publication. Make your change, add the article back to the publication, then run sp_refreshsubscriptions.

    This should force a snapshot of just this table and send it to the subscribers.

    Monday, June 15, 2020 9:21 PM
    Moderator
  • Hi Wayne E. Pfeffer,

    You can try Hilary Cotter's suggestion, however I have my own views as next.

    For your case, I guess you use one transaction replication type. Right? So you can not alter the table because it is being published.

    There are some methods about altering tables which in replication:

    1.Create one new table and then create a new publication;
    2.If you have only few subs, you can choose alter the table one the subs’ side;
    3.Suggest you create one merge type publication for your case, then you can alter the table both on the pubs’ side and subs’ side. Maybe you want to add another new column in the future, we cannot predict the future business, but we can prepare for its development.

    Merge Type On my side:

    Best Regards.

    m


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 16, 2020 2:36 AM
  • Hi Wayne E. Pfeffer,

    Is the reply helpful?

    Best Regards.

    m


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 17, 2020 1:13 AM
  • Hi Wayne E. Pfeffer,

    Is the reply helpful?

    Best Regards.

    MIAOYUXI


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, June 19, 2020 1:18 AM