Convert Foreign Key to Check Constraint

Reason for converting a foreign key to a check constraint

• After partitional a table where the primary key was modified. If an additional column was added to the primary key, all the foreign key reference will now error out. You can either drop the foreign key or create a CHECK constraint insteac.
• If the table has been broken into 2 different tables, one for transaction and one for archive. You now have to check in multiple tables for the id check. Create a check constraint to look at multiple tables instead of just one with foreign key constraint.

Examples

Drop an existing Foreign Key constraint and Create a Check constraint instead.

--ALTER TABLE [dbo].tbTable01   WITH CHECK ADD  CONSTRAINT [FK_tbTable01_tbTable02] FOREIGN KEY([biTable02Id])
--REFERENCES [dbo].[tbTable02] ([biTable02Id])
--GO

ALTER TABLE [dbo].[tbTable01]  WITH CHECK ADD CONSTRAINT [CHECK_tbTable01_tbTable02] CHECK (([dbo].[fnChecktbTable02]([biTable02Id])=(1)))
GO

ALTER TABLE [dbo].[tbTable01] CHECK CONSTRAINT [CHECK_tbTable01_tbTable02]
GO

New check function for the check constraint.

CREATE FUNCTION [dbo].[CHECK_tbTable01_tbTable02]
  (@biTable02Id bigint)
RETURNS bit
AS
BEGIN
  IF EXISTS(SELECT * FROM tbTable02 (NOLOCK) WHERE biTable02Id = @biTable02Id)
    RETURN 1
  RETURN 0
END
GO