March 25, 2015 at 10:10 am
I was bitten by the delights of NOCHECK recently and while writing an article about the behaviour it causes I noticed the syntax from SSMS when it scripts a FK:
ALTER TABLE [dbo].[StockItems] WITH NOCHECK ADD CONSTRAINT [FK_Location] FOREIGN KEY([Location])
REFERENCES [dbo].[Locations] ([ID])
GO
ALTER TABLE [dbo].[StockItems] CHECK CONSTRAINT [FK_Location]
GO
What is the point of the second ALTER TABLE? The FK is enabled within the first statement and I don't see that the second statement adds anything to it.
I'm just making sure I haven't missed something really, really basic.
Thanks.
March 27, 2015 at 7:55 am
The first statement will create the FK.
Maybe they include second for information to let the reader know it is enabled?
If you do
ALTER TABLE [dbo].[MyTable] WITH CHECK ADD CONSTRAINT [FK_MyTable_OtherTable] FOREIGN KEY([MyColumn])
REFERENCES [dbo].[OtherTable] ([OtherColumn])
then your FK will not be created if there is invalid data in MyColumn.
Additionally, if you want to mark it as trusted ( optimisation benefits if the FK is trusted under certain scenarios) then you will have to
ALTER TABLE [dbo].[MyTable] CHECK CHECK CONSTRAINT [FK_MyTable_OtherTable]
Notice the Double check.
March 27, 2015 at 8:08 am
That's what I thought, but it isn't what I'm seeing.
I deliberately have an invaid entry when I apply the FK (with NOCHECK), just to see what happens.
sys.foreign_keys has 'is_not_trusted' set to 1, as expected.
ALTER TABLE [dbo].[StockItems] CHECK CONSTRAINT [FK_Location] completes successfully and 'is_not_trusted' is still 1, which is just as well, with invalid data in the table.
ALTER TABLE [dbo].[StockItems] WITH CHECK CHECK CONSTRAINT [FK_Location] fails with an error, until I remove the incorrect data and 'is_not_trusted' sets to 0.
If I remove the corrupt row, create a FK with NOCHECK and then run ALTER TABLE [dbo].[StockItems] CHECK CONSTRAINT [FK_Location] , it completes, but 'is_not_trusted' remains at 1.
So I don't see the point of the script creating ALTER TABLE [dbo].[StockItems] CHECK CONSTRAINT [FK_Location] - it doesn't appear to do anything in this case.
March 27, 2015 at 8:16 am
Sorry, edited my post.
I think it is for information because when I script a disabled constraint then the follow up alter table has NOCHECK.
Maybe someone who coded the scripting feature in SSMS decided that it was too much hassle to only script out the disable script if the constraint is disabled, and hence scripts it out for all.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply