7/22/2014

T-SQL Add Drop Constraints

When building a Data Warehouse, there are times when you must drop and add Foreign Key constraints.

Here's how you check to see if the Constraint exists, if it does, Drop it:


IF EXISTS (SELECT 1 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where
       CONSTRAINT_NAME = 'FK_Posted Claim Fund_Vendor')
ALTER TABLE [fact].[FactPostedClaimFund] DROP CONSTRAINT [FK_Posted Claim Fund_Vendor]
GO

Likewise, to see if the Constraint doesn't exist, Add it:


IF NOT EXISTS (SELECT 1 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where
       CONSTRAINT_NAME = 'FK_Posted Claim Fund_Vendor')

ALTER TABLE [fact].[FactPostedClaimFund]  WITH CHECK ADD  CONSTRAINT [FK_Posted Claim Fund_Vendor] FOREIGN KEY([VendorSK])
REFERENCES [dim].[DimVendor] ([VendorSK])
GO
ALTER TABLE [fact].[FactPostedClaimFund] CHECK CONSTRAINT [FK_Posted Claim Fund_Vendor]
GO

This technique is quite handy.

No comments:

Post a Comment