如何处理Access中插入的参照完整性 [英] How to handle Referential Integrity for Inserts in Access

问题描述

我正在做一个我从同事那里接手的Access项目.存在三个表:rules，overview和relationship. relationship表具有两个字段，每个字段都是外键，链接到其他两个表中的主键.我以表单的形式在rules表的数据表视图中，可以毫无问题地删除记录.但是，当我尝试将记录插入到rules表中时，该记录将被插入到rules表中，但是没有匹配的记录插入到relationship表中.我已选中强制引用完整性"，以及级联更新相关字段"和级联删除相关记录".我天真地假设这将处理插入，但是显然我错了.因此，我现在想知道处理此问题的最佳方法-我是否为表单的After Insert事件编写一些VBA，从而将记录相应地插入到relationship表中?

I'm working on an Access project that I took over from a co-worker. There are three tables that exist: rules, overview and relationship. The relationship table has two fields, each is a foreign key that links to a primary key in the other two tables. I have a datasheet view of the rules table in a form, where I can delete records with no problems. However, when I try to insert a record into the rules table, the record will be inserted into the rules table, but there is no matching record inserted into the relationship table. I have "Enforce Referential Integrity" checked, as well as "Cascade Update Related Fields" and "Cascade Delete Related Records". I made a naive assumption that this would handle inserts, but clearly I was wrong. So I'm now wondering about the best way to handle this - do I write some VBA for the After Insert event of the form that inserts a record into the relationship table accordingly?

推荐答案

通常的方法是采用一种形式将记录插入到基于包含关系表的查询的规则的规则中，例如，用户可以选择相关的概述，或具有合适的主/子字段的表单/子表单设置.在NorthWind示例数据库中，订单详细信息"表是关系"表的一个示例，它使用了讨厌的在表中查找"反功能，但您可能会得到一些进一步研究的想法.

The usual way is to either have a form to insert records into rules that is based on a query that includes the relationship table and, say, a combo that allows the user to select the relevant overview, or a form / subform set-up with suitable master / child fields. In the NorthWind sample database, the Order Detail table is an example of your Relationship table, it uses the loathed look-up-in-table anti-feature, but you may get some ideas for further research.

选项1的详细说明

表格

概述
ID
概述

Overview
ID
Overview

规则
ID
规则

Rules
ID
Rule

关系
两个FK组成的RulesID)PK
OverviewID)

Relationship
RulesID ) PK formed by two FKs
OverviewID )

关系

数据

建议1查询设计

请注意，Relationship中的两个字段都包含在查询中.不必从规则中显示ID，因为它是一个自动编号字段，但为简单起见，在此将其包括在内.

Note that both fields from Relationship are included in the query. It is not necessary to show ID from rules, because it is an autonumber field, but it is included here for simplicity.

如果删除一行，则两个表中的记录都将被删除.

If a row is deleted, records from both tables will be deleted.

您不能违反参照完整性.您需要先创建所有概述，然后才能使用它，或者提供其他添加概述的方法.

You cannot violate referential integrity. You will need to have all overviews created before this will work, or provide a different method of adding Overviews.

如果更新RulesID和OverviewID，则会将一条记录添加到关系"表中，但不添加到规则"中.

If you update RulesID and OverviewID, a record will be added to the Relationship table, but not to Rules.

如果更新OverviewID和Rule，记录将被添加到关系"和规则"中.

If you update OverviewID and Rule, records will be added to both Relationship and Rules.

如果创建连续表单，则可以通过更加友好的方式和更多的控制来实现上述所有功能.您可以使用组合框来允许用户选择更友好的概述描述，而不是ID，并且可以利用NotInList事件添加新的概述.

If you create a continuous form, you have all the above in a much more user-friendly way with more control. You can use a combobox to allow the user to select the more friendly description of overview, rather than the ID and you can take advantage of the NotInList event to add new Overviews.

请注意，到目前为止，这不需要一行代码.这就是Access的力量.

Note that so far this has not needed a single line of code. That is the power of Access.

这篇关于如何处理Access中插入的参照完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！