locked
Merge Replication - Update Foreign Key Constrant RRS feed

  • Question

  • Hi,

    I need to change the delete and update rules on a foreign constraint in a replicated database (merge). The publication is setup to replicate scheme changes and the subscribers use web sync via IIS.  I have made schema changes in the past with no issues but this is the first time I need to use a drop command.  The script I'm looking to run is as follows:

    ALTER TABLE dbo.ProjectTakeoff
    	DROP CONSTRAINT FK_ProjectTakeoff_Projects
    GO
    ALTER TABLE dbo.ProjectTakeoff ADD CONSTRAINT
    	FK_ProjectTakeoff_Projects FOREIGN KEY
    	(
    	ProjectAID,
    	ProjectLID
    	) REFERENCES dbo.Projects
    	(
    	ProjectAID,
    	ProjectLID
    	) ON UPDATE  NO ACTION 
    	 ON DELETE  NO ACTION 	
    GO
    

    Is this script safe to run on the publisher DB or will I need to reinitialize the subscriptions?

    Thanks is advance.

    Tim

    Friday, August 11, 2017 4:28 PM

Answers

  • Tim,

    As I understand your question, ALTER schema changes are supported, but DROP schema changes are not supported, according to this document:
    Make Schema Changes on Publication Databases

    Therefore, you will need to reinitialize the subscription, to get replication working again.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, August 11, 2017 5:17 PM
    • Proposed as answer by Teige Gao Monday, August 14, 2017 8:41 AM
    • Marked as answer by TSD123 Monday, August 14, 2017 8:43 AM
    Friday, August 11, 2017 5:15 PM

All replies