Foreign Key is enabled , but not trusted

  • Is there any reason to have a foreign key enabled, but not trusted?

  • when the FK was originally created, it would default to checking all the data, so it would be enabled and trusted.
     some processes will disable foreign keys as part of large Insert loads to minimize locking, but they might enable the key again, but forget to enable with the CHECK option.
    i suspect that's what you are seeing.
    it would be best to re-enable the FK with the check option to make the key trusted again.

    the syntax looks like this for a FK: the CHECK CHECK is correct, but looks weird
    ALTER TABLE [X12].[EDI820Entity] WITH CHECK CHECK CONSTRAINT [FK__EDI820Entity__EDI820DataID];

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • When doing bulk load with a lot of data, i.e. data warehouse, it can be turned off to improve performance.
    Couple of write ups on the topic:
    http://www.sqlservercentral.com/blogs/waterox-sql/2014/10/17/untrusted-foreign-keys/
    https://www.brentozar.com/blitz/foreign-key-trusted/

  • Okay and thanks for the responses! I'm in a new gig and came across a Database that has hundreds so it caught me off guard and made me wonder why it would be like this.

    Thanks again!

  • butcherking13 - Friday, March 3, 2017 11:31 AM

    Okay and thanks for the responses! I'm in a new gig and came across a Database that has hundreds so it caught me off guard and made me wonder why it would be like this.

    Thanks again!

    Just in case.... if it is CRM then it was common up to a particular version of CRM 2013 - they had been set as "not for replication" which then sets the not trusted.

    This is acknowledged by MS as a bug and they will supply a script on demand to the customers that raise a request for it.

    On quite large installations this one is a killer for some of their CRM entities

  • here's a script to generate the commands for you;
    I keep this handy all the time:
    SELECT Quotename(s.NAME) + '.' + Quotename(o.NAME) AS QualifiedObject,
        s.NAME             AS SchemaName,
        o.NAME             AS ObjectName,
        i.NAME             AS ConstraintName,
        i.[type_desc],
        '' AS ConstraintDefinition,
        'ALTER TABLE ' + Quotename(s.NAME) + '.'
        + Quotename(o.NAME)
        + ' WITH CHECK CHECK CONSTRAINT '
        + Quotename(i.NAME) + ';'       AS Fix,
        --'' AS ConstraintTest
        'SELECT * FROM ' + Quotename(s.NAME) + '.' + Quotename(o.NAME) + ' WHERE ' + Quotename(Col_name(colz.parent_object_id, colz.parent_column_id)) + ' NOT IN(SELECT ' + Quotename(Col_name(colz.referenced_object_id, colz.referenced_column_id)) + ' FROM ' + Quotename(Object_name(i.referenced_object_id)) +') ' AS ConstraintTest
      --,'[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
      FROM sys.foreign_keys i
        INNER JOIN sys.objects o
           ON i.parent_object_id = o.object_id
        INNER JOIN sys.schemas s
           ON o.schema_id = s.schema_id
        INNER JOIN sys.foreign_key_columns colz ON i.parent_object_id = colz.parent_object_id AND i.object_id = colz.constraint_object_id
      WHERE i.is_not_trusted = 1
        AND i.is_not_for_replication = 0

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply