Hello all, about the is_not_for_replication=1 and is_not_trusted=1 foreign key constraints
The following query will show all the FK Constraints on a given database that are not trusted.
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
, is_disabled, is_not_trusted, is_not_for_replication
FROM sys.foreign_keys AS i
JOIN sys.objects AS o ON i.parent_object_id = o.object_id
JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1
This can happen for 2 reasons.
1. The constraints were created without the "CHECK CHECK" option
OR
2. The is_not_for_replication=1 already, which automatically makes it untrusted.
In CRM 2011/2013 I have noticed that for ALL of them even in the the ootb databases, they are all set to is_not_for_replication and is_not_trusted to true.
There are few articles out there that say this is bad mojo and a client of ours is now looking into it.
Here is an article on it in general SQL, but not specific to CRM:
https://sgdevaney.wordpress.com/2012/04/06/sys-foreign_keys-is_not_trusted/
Our client (rightfully so) thinks they may benefit performance wise from setting all the constraints to = 0 for both options, which unfortunately can only happen by dropping and recreating the constraints, which i'm pretty sure is unsupported.
Their other question is why on earth would MS default it this way? Again, I checked our "Virgin 2013 and 2011" dbs on our servers and sure enough other than 25 constraints owned by the metadataschema, all were untrusted...which causes performance issues like this guy talks about in the article....crazy.
Summary = Why is it this way? Does it need to remain this way? Is it supported to make it not this way?
