Thursday, November 15, 2018

Which foreign keys reference this table?

BIG task yesterday... ran into some data corruption that fortunately could be repaired by dropping/recreating the table's primary key, but -- that table had 469 foreign keys!  Try to drop the PK on any table with FK references, and you will fail.  For example, here's the error when I try to drop the PK on Person.Address in the AdventureWorks db:

      Msg 3725, Level 16, State 0, Line 18
      The constraint 'PK_Address_AddressID' is being referenced by table      
         'SalesOrderHeader', foreign key constraint 'FK_SalesOrderHeader_Address_ShipToAddressID'.
      Msg 3727, Level 16, State 0, Line 18
      Could not drop constraint. See previous errors.

Ok. So I need to drop/recreate FK_SalesOrderHeader_Address_ShippToAddressID first,  but are there more?  How do I know all of the foreign keys that reference another table's primary key?  Easy sneasy.  Just input the table and schema name from the table you are dropping the PK from.      

     EXEC sp_fkeys @pktable_name = 'Address',@pktable_owner = 'Person'

It's cut off, but you get the point.  These are the foreign keys that I must also address if I am going to drop/recreate the Person.Address primary key.  It's just a few, so easy enough to write up the code for the DROP/CREATE of the foreign keys... but what about that table with 469 foreign keys?!  NOT.  

Check out this post for a great piece that will generate the statements to both drop and recreate the referencing foreign keys for the given @PKname.  It was certainly helpful yesterday! 

And see this for more details on sp_fkeys.   sp_fkeys

No comments:

Post a Comment