How to correctly add a foreign key in sql server
When you script a table that has a foreign key, SQL Server Management studio will generate the following code:
ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD
CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
GO
followed immediately by :
ALTER TABLE [Production].[ProductCostHistory] CHECK CONSTRAINT
[FK_ProductCostHistory_Product_ProductID]
GO
Why the the last 3 lines?
The last 3 lines enable an existing foreign key, but does not check if the data in the table is consistent with the foreign key.
If you want to do that you would have to write:
ALTER TABLE [Production].[ProductCostHistory] WITH CHECK CHECK CONSTRAINT
[FK_ProductCostHistory_Product_ProductID]
GO
Yes, this seems strange “CHECK CHECK”, but this is how the documentation states it should be written:
https://dba.stackexchange.com/questions/167861/what-is-a-with-check-check-constraint
I think the lines are generated for safety (sometimes foreign keys are disabled en not enabled again, this will cause the query optimizer to not use these untrusted foreign keys), but I think this should be done in a monitoring scripts not in a deployment script for a new version of the database.
Will dropping a table remove constraints in SQL Server?
When you drop a table in SQL Server, all primary keys, foreign keys, defaults etc are removed from the database.