Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CHECK CONSTRAINT Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 11:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
Comments posted to this topic are about the item CHECK CONSTRAINT
Post #672172
Posted Tuesday, March 10, 2009 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 6,048, Visits: 8,328
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #672239
Posted Tuesday, March 10, 2009 4:06 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 8:09 AM
Points: 664, Visits: 48
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 : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113154

Plz correct me if I am wrong.

-Navnath
Post #672256
Posted Tuesday, March 10, 2009 6:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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. [...]
Post #672362
Posted Wednesday, March 11, 2009 9:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
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
Post #673406
Posted Wednesday, March 11, 2009 9:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:21 AM
Points: 291, Visits: 479
Answers C and D appear to be identical...

Disable CHECK constraint at the Subscriber database
Post #673412
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse