MSSQLTips

How we can find the "Check Existing Data On Creation or Re-enabling" attribute of one FK from SQL server metadata ?

The sys.foreign_keys.is_disabled holds the "Enforce Foreign Key Constraint" attribute of the FK. 

Thanks in advanced.

 I think I understand what you're looking for..... you can use the objectproperty function

select case objectproperty(object_id('<my foreign key>'),'CnstIsDisabled') when 0 then 'Constraint is active' else 'Constraint is disabled' end

The "objectproperty(object_id('<my foreign key>'),'CnstIsDisabled')" is the same with the "select is_disabled from sys.foreign_keys where name= '<my foreign key>'".

Both returns the attribute "Enforce Foreign Key Constraint". I try to find out how to get the attribute "Check Existing Data On Creation or Re-enabling".

Example:

 If I create a FK with this code

ALTER TABLE [Sales].[SalesOrderDetail] WITH NOCHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY ([SpecialOfferID],[ProductID]) REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID],[ProductID]) ON UPDATE NO ACTION ON DELETE NO ACTION

 ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]

 Then the attribute "Check Existing Data On Creation or Re-enabling" is false (0) that means that the system should not check the existing data on creation (or on re-enabling) of the constraint , and the attribute "Enforce Foreign Key Constraint" is true (1) that means that the system should check all new records for FK violation.