Post reply

Constraints Missing from Transactional Replication

  • October 20, 2004 at 6:54 pm

    #85693

    Environment: Transactional replication between 2 SQL Server 2000 (SP3a) systems (both running on Windows 2000 sp4).

    Situation: I have a Unique Constraint specified on TableA in my Publisher database.  When TableA is replicated to my Subscription Database the Unique Constraint is not there.  I have checked the Scripts that the Snapshot Agent creates (.sch, .idx) for TableA and the Unique Constraint is not in the script.  All Indexes on TableA are properly scripted out and applied to the Subscriber.

    Why is the Unique Constraint not getting scripted out?  (Especially since SQL Server creates an Index for any Unique Constraint anyway).  I have tried changing the "@schema_option" parameter in the sp_addarticle (I have my entire publication scripted out) to include the "Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements." (0x8000) option, but that did not help.  I would rather not have to script out the creation of the Constraints in a Post Replication script if possible. 

    Any help would be appreciated.

    Mike

  • October 20, 2004 at 10:11 pm

    #527393

    In Transactional Replication, the Subscription Database becomes a "Logically read only" Database. Therefore, because there will be no Inserts, Updates or Deletes happenning at the Subscriber, the Unique Constraint is not required. You will also notice that various other attributes will not replicate to the subscriber like, Identity Columns become Integers etc.

    However, If you have setup Transactional Replication with Immediate Updating Subscribers, then the constraints should be at the subscriber.

    Make sense?

    Kindest Regards,

  • October 21, 2004 at 7:10 am

    #527450

    Run sp_helpconstraint. If Status_For_Replication is (n/a) then if won't be propagated to the subscriber. You should recreate it without specifying  the NOT FOR REPLICATION clause.

  • October 21, 2004 at 1:42 pm

    #527533

    racosta,

    It was not created with the NOT FOR REPLICATION property.  I don't think that is even possible since the "NOT FOR REPLICATION" property only applies to IDENTITY Columns, Check Constraints & Foreign Key Constraints.

    Mike

  • October 25, 2004 at 6:17 pm

    #527892

    The NOT FOR REPLICATION property also applies to Triggers!

    Kindest Regards,

  • October 25, 2004 at 9:53 pm

    #527902

    Have you re-initialised the subscription ?  I don't think the change you made will have any impact to existing subscriptions.  This will most likely result in all data being sent to your subscriber again (this depends on what you told the publication to do when it finds that the table already exists at the subscriber)

    I have just created a publication on a table that has a unique constraint. Replication managed to create a unique constraint on the replica - you need to either set the properties for each article or set the default for all articles so that declared referential integrity is included. 

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply