locked
sys.foreign_keys field NULL RRS feed

  • Question

  • Here's the deal:

    I manage a third party software that runs on SQL Server. Usually, there are updates of that said software, and in one of those i discovered a consistency error, that called for a checkcatalog command. Long story short, i found the culprate. It seems that a foreign key declared in one of the tables is causing the trouble. The foreign key is lodged in the sys.foreign_keys with the field refrecend_object_id and key_index_id set as null, and therefor, with no reference in the sys.foreign_key_columns table. 
    Now, here are the things I tried:

    1 -  I know what values to load in the field, but i don't seem to e able to edit the table 'ad hoc' .Tried "allow updates"command and update the field, with no effect
    2 - Backup and restore is worthless. And no backups exist prior to this condition
    2 - Tried to drop the PK, no use, still calls for DBCC checkcatalog



    Any help? Please?
    Thursday, February 13, 2014 10:11 PM

Answers

  • Have you attempted to DROP and RE-CREATE the foreign key constraint?

    sys.foreign_keys is a catalog view which is why you are unable to edit it.

    If you really wanted to edit the data you would need to identify and edit the underlying physical tables.

    Executing the following code in the master database reveals the VIEW definition.

    select object_definition(OBJECT_ID('sys.foreign_keys'));

    OUTPUT:

    CREATE VIEW sys.foreign_keys 
    AS 
      SELECT o.name, 
             o.object_id, 
             o.principal_id, 
             o.schema_id, 
             o.parent_object_id, 
             o.type, 
             o.type_desc, 
             o.create_date, 
             o.modify_date, 
             o.is_ms_shipped, 
             o.is_published, 
             o.is_schema_published, 
             f.indepid    AS referenced_object_id, 
             f.indepsubid AS key_index_id, 
             o.is_disabled, 
             o.is_not_for_replication, 
             o.is_not_trusted, 
             o.delete_referential_action, 
             -- ERefAct    d.name AS delete_referential_action_desc,    
             o.update_referential_action, -- ERefAct    
             u.name AS update_referential_action_desc,    
             o.is_system_named   
             FROM sys.objects$ o   
             LEFT JOIN sys.syssingleobjrefs f ON f.depid = o.object_id AND f.class = 27 AND f.depsubid = 0 -- SRC_FK_REFD_INDEX   
             LEFT JOIN sys.syspalvalues d ON d.class = 'FKRA' AND d.value = o.delete_referential_action   
             LEFT JOIN sys.syspalvalues u ON u.class = 'FKRA' AND u.value = o.update_referential_action   
             WHERE o.type = 'F '  

    As you can see. There are a number of objects that you will need to consider.

    Before you consider going down the rabbit hole, I strongly advise you to backup the existing database AND run a complete CHECKDB operation. The issues you are seeing could be the just the tip of the iceberg.

    DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting






    • Edited by John Sansom Friday, February 14, 2014 8:50 AM
    • Marked as answer by Sofiya Li Monday, February 24, 2014 9:56 AM
    Friday, February 14, 2014 8:22 AM