Creating foreign keys ensure data integrity across different tables in a database. However only creating these is not enough. Keeping in view the requirement we have to configure proper impact on foreign key value when its primary key is affected by update or delete operation.
SQL Server provides four rules for implementing the effect of primary key on foreign key value in case of update or delete operation. These are
- No Action
- Set NULL
- Set Default
In my related article on MSSQLTips, I have discussed the configuration and effect of these four rules. The emerged problem is to analyze the existing foreign keys for their configured rules for update and delete operations. It is required to generate a list of all foreign keys along with their associated columns and rules for update and delete operations. Below is the script that would provide a brief information to start with
SELECT fkey.name AS FKeyName, object_name(fkey.parent_object_id) AS FKeyTable, cols.name AS FKeyColumn, object_name(fkey.referenced_object_id) AS PKeyTable, colsa.name AS PKeyColumn, CASE is_disabled WHEN 1 THEN 'YES' WHEN 0 THEN 'No' END AS IsDisabled, delete_referential_action_desc AS DeleteRule, update_referential_action_desc AS UpdateRule FROM sys.foreign_key_columns FKeyC INNER JOIN sys.foreign_keys fkey ON fkeyc.parent_object_id = fkey.parent_object_id AND fkeyc.referenced_object_id = fkey.referenced_object_id INNER JOIN sys.all_columns cols ON fkeyc.parent_object_id = cols.object_id AND parent_column_id = cols.column_id INNER JOIN sys.all_columns colsa ON fkeyc.referenced_object_id = colsa.object_id AND parent_column_id = colsa.column_id ORDER BY is_disabled DESC, delete_referential_action_desc, update_referential_action_desc
You may also add create_date and modify_date in the select list for creation and modification date of respective foreign key. Once the list is available, analyze the requirement and configured rule along with disabled foreign keys.