• Comments posted to this topic are about the item CHECK CONSTRAINT

  • I don't have a test setup here to quickly double-check, but if I read the documentation correctly the NOT FOR REPLICATION will not cause the constraint to exist only at the publisher. Here's the quote:

    "The check constraint is not enforced when a replication agent performs an insert, update, or delete operation."

    I interpret this as "the constraint itself is replicated to the subscriber and it will also be enforced there with respect to 'local' (manual) inserts and updates, but inserts and updates from a replication agent are explicitly exempted".

    Can someone who can easily create a test setup please double check if this is a documentation error, or just a badly formulated QotD?

    Oh, and Steve might also want to fix the two identical answer options in this question...

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog:
    SQL Server Execution Plan Reference:

  • I have not cross checked it but gone through some of the articles.

    in one of blog I found that Constraints should be on Subscriber database.

    Plz refer :

    Plz correct me if I am wrong.


  • Unfortunately I don't have a test setup at hand either and perhaps I'm again misinterpreting the information in the MSDN article the QotD is referring to, but in my opinion a 'choose all that apply' would have been the proper option for the question:

    The NOT FOR REPLICATION option can be specified in the following ways:

    - [...]

    - Directly in Transact-SQL syntax or in Microsoft SQL Server Management Studio when:

    - Creating or modifying an object in the publication database.

    - Creating or modifying an object in the subscription database. [...]

  • This is a BAD formulated question. I got right because I guessed the intentions of the author. The constraint DOES gets created on the subscriber just not enforced!

    If you don't want the definition at all on the subscriber, you suppress it with the schema_option parameter of the article.

    * Noel

  • Answers C and D appear to be identical...

    Disable CHECK constraint at the Subscriber database

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

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