April 11, 2014 at 4:50 am
Unless I misunderstand (which is entirely possible), when creating a FK constraint, the WITH CHECK/NO CHECK option says whether or not the data should be evaluated when the constraint is created.
Yesterday I had a delete statement on the parent table throw a FK Contraint error. When I looked into the delete trigger on the parent table it looked fine:
delete child from child, deleted where child.key=deleted.key
The only difference between this set of table and others was that someone had created the FK Contraint on the child table with the CHECK option. Other tables in the DB have the no check option. I dropped the constraint and re-added it with the NO CHECK option and the problem went away.
Do I misunderstand the CHECK option or are there gremlins afoot?
Thanks
ST
April 11, 2014 at 6:09 am
Nevermind. The issue was caused by the FK constraint being enabled NOT by the constraint CHECK option.
April 11, 2014 at 6:32 am
Ah, that makes sense. I was following this question because I was curious if it worked differently than my understanding too. Good to know we both have a handle on it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply