locked
Add Primary Key To Replicated Table RRS feed

  • Question

  • I goofed about a year ago when I created a table without a primary key set.  The full table is now an article in a merge publication.  I am now having issues with using it as a data service and I want to correct it. I am using SQL 2008 Workgroup.
    Management Studio 2008 warns me about a validation error if I try to add a primary key to the table on the publisher:

    'CompanyListing' table
    - Error validating the default for column 'rowguid'.

    Should I go ahead and proceed despite the warning, and if not is there an easy way to fix this or do I need cancel replication, fix the table, then republish?


    Tuesday, October 4, 2011 3:41 PM

Answers

  • Hi Kilmeister,

    Schema changes must be made using ALTER syntax (rather than SSMS) on the publisher and they will propagate to subscribers.  Please see Making Schema Changes on Publication Databases.

    For example, a primary key constraint for a published Merge article can be created by executing:

    USE MyPublicationDB
    GO
    
    ALTER TABLE dbo.class
    ADD CONSTRAINT pk_class PRIMARY KEY CLUSTERED (class_id);
    GO
    

    On the next sync the primary key constraint will replicate to subscribers.


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by jeffatturbofish Wednesday, October 5, 2011 8:28 PM
    • Marked as answer by Stephanie Lv Tuesday, October 11, 2011 11:47 AM
    Wednesday, October 5, 2011 2:39 AM