sql - 如何确定现有访问关系的 ON UPDATE/DELETE 操作？

Question

如何为 MS Access 中的外键关系约束获取 ON UPDATE 操作和 ON DELETE 操作？

这是我到目前为止所拥有的：

SELECT szRelationship as ConstraintName,
szObject as TableName,
szColumn as ColumnName,
szReferencedObject as ParentTableName,
szReferencedColumn as ParentColumnName
FROM MSysRelationships
where szObject not like 'MSys%'

标签： sqlms-access

Answer 1

引用完整性详细信息似乎显示在 [MSysRelationships] 表的 [grbit] 列中：

MSysRelationships.grbit

bit  value  meaning
---  -----  -----------------------------------------------
  0      1  1 = one-to-one relationship (0 = one-to-many)
  1      2  don't enforce Referential Integrity
  8    256  ON UPDATE CASCADE
 12   4096  ON DELETE CASCADE

所以，例如，

• 与 ON UPDATE CASCADE 和 ON DELETE CASCADE 的一对多关系的值为 1000100000000 = 4352
• 与 ON UPDATE CASCADE 和 ON DELETE CASCADE 的一对一关系的值为 1000100000001 = 4353。