如何查询Access现有外键关系的ON UPDATE/DELETE动作？

Got it, let's tackle your two needs: identifying the ON UPDATE/DELETE actions for existing Access relationships, and fetching those settings specifically for foreign key constraints. Your initial SQL query lays a solid foundation, but we need to add the critical fields that capture those action rules.

Key Background on MSysRelationships Fields #

The MSysRelationships system table stores all relationship details, including two integer fields that define referential integrity actions:

• ibUpdateRule: Controls the ON UPDATE action
• ibDeleteRule: Controls the ON DELETE action

These integer values map to human-readable actions like this:

• 0 = No Action
• 1 = Cascade
• 2 = Set Null
• 3 = Set Default

Expanded SQL Query #

Here's the updated query that includes these action rules, with a CASE statement to translate the integers into clear, easy-to-understand labels:

SELECT 
    szRelationship as ConstraintName,
    szObject as TableName,
    szColumn as ColumnName,
    szReferencedObject as ParentTableName,
    szReferencedColumn as ParentColumnName,
    CASE ibUpdateRule
        WHEN 0 THEN 'No Action'
        WHEN 1 THEN 'Cascade'
        WHEN 2 THEN 'Set Null'
        WHEN 3 THEN 'Set Default'
        ELSE 'Unknown'
    END as OnUpdateAction,
    CASE ibDeleteRule
        WHEN 0 THEN 'No Action'
        WHEN 1 THEN 'Cascade'
        WHEN 2 THEN 'Set Null'
        WHEN 3 THEN 'Set Default'
        ELSE 'Unknown'
    END as OnDeleteAction
FROM MSysRelationships 
WHERE szObject NOT LIKE 'MSys%'

How to Use This Query #

1. Open your Access database
2. Create a new query, then switch to SQL View
3. Paste the query above and run it
4. The results will display every non-system table relationship, with explicit labels for both the ON UPDATE and ON DELETE referential integrity actions

This query directly addresses both of your requirements: it pulls all relevant foreign key relationships and clearly shows their update/delete constraint behaviors.

内容的提问来源于stack exchange，提问作者Greg Finzer