none
Can't Enable Foreign Key Constraint (Msg 547, Level 16, State 0, Line 15) RRS feed

  • Question

  • Many foreign keys in our prod databases are not being used. Historically, the integrations team would, in certain circumstances disable foreign keys, insert massive amounts of data, then re-enable the keys. unfortunately, they were not re-enabled properly or many databases were left with FK  Constraint disabled and they are not being used (resulting in bad query plans).

    I have been tasked to find the databases and tables that contain disabled FK and found couple already. Since I'm not a developer I just wanted to ensure all due diligence before enabling the FK in prod. so I took following steps:

    1.  Restore the prod to QA and tested enabling all disabled foreign keys in the restored db. 
    2. Ran the following statement to enable all FKs that were disabled:

    --ENABLE ALL FOREIGN KEYS WITH CHECK CONSTRAINT

    USE [MyDB] EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

    Got the folllowing error:

     

    Msg 547, Level 16, State 0, Line 15
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.AudLogs_dbo.ActEnroll_ActEnroll_Id". The conflict occurred in database "MyDB", table "dbo.ActEnroll", column 'Id'.

    Googling the error landed me to couple of solutions (link below) but I'm really not that expert and I need a little more detailed guidance and if possible steps to resolve this issue.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fbaf71cd-333b-4b37-a83c-63e9478dbd4f/mislead-by-msg-547-error-on-alter-table-add-constraint?forum=transactsql

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50d40f31-acc7-4f8c-b75a-36b155c757db/the-alter-table-statement-conflicted-with-the-foreign-key-constraint-fkbookinghistorybookingref?forum=transactsql

    since this is a prod environment I'm not sure if dropping or adding any records from Parent or Child table will be a good solution unless I can find a way to ensure that it is a bad data I guess.  Or No Check will also be a bad option as it will compromise the data integrity. 

    I would appreciate if I can get some guidance to handle this issue. 

    Tuesday, October 29, 2019 9:57 PM

Answers

  • Given the sloppiness in the procedures you describe, I am not surprised that you got that error.
    First, make sure that you find all of these errors. (I bet that there is more than one.)

    Next for each run a query like

    SELECT *
    FROM   child c
    WHERE  NOT EXISTS (SELECT *
                       FROM   parent p
                       WHERE  p.keycol = c.keycol
                         AND  p.keycol2 = c.keycol2
                         ...)

    Save the data into an Excel book or so.

    Then find some people who can claim ownership of the data to discuss how this is to be repaired. Should orphans be deleted? Should missing parents be added? Etc. You may also have to talk to the application devs how your changes may affect the application.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 29, 2019 10:59 PM

All replies

  • Given the sloppiness in the procedures you describe, I am not surprised that you got that error.
    First, make sure that you find all of these errors. (I bet that there is more than one.)

    Next for each run a query like

    SELECT *
    FROM   child c
    WHERE  NOT EXISTS (SELECT *
                       FROM   parent p
                       WHERE  p.keycol = c.keycol
                         AND  p.keycol2 = c.keycol2
                         ...)

    Save the data into an Excel book or so.

    Then find some people who can claim ownership of the data to discuss how this is to be repaired. Should orphans be deleted? Should missing parents be added? Etc. You may also have to talk to the application devs how your changes may affect the application.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 29, 2019 10:59 PM
  • Hi Erland,

    Thank you very much for the detailed guidance, I do indeed agree that the procedure was sloppy :).

    Couple more questions: 

    1. Do we need any downtime for Enabling the FK Constraint in Prod or the database will be available while running command to enable the constraints?

    2. Will dropping and recreating Constraint give same error?  If not would you recommend this? 

    3. Going forward I'm thinking of creating a SQL Agent job to monitor and send an email alert if there are any databases that has foreign keys disabled, do you have any other suggestions? 

    Thank you!


    • Edited by Shaddy_1 Wednesday, October 30, 2019 2:44 PM
    Wednesday, October 30, 2019 2:22 PM
  • 1. Do we need any downtime for Enabling the FK Constraint in Prod or the database will be available while running command to enable the constraints? 

    Yes, if the tables are of any size, there will be trouble if you do this when the system is live, since you will hold a Sch-M look on the table during the operation and block all other access to the table.  So do this in a maintenance window.

    2. Going forward I'm thinking of creating a SQL Agent job to monitor and send an email alert if there are any databases that has foreign keys disabled, do you have any other suggestions? 

    Certainly. Just make sure that it does not run when the other team are doing their loads the disable keys.

    If you want them to stop disabling FKs at all, you could consider DDL triggers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 30, 2019 10:52 PM