如何处理 Access 中插入的引用完整性
我正在从事一个从同事那里接手的 Access 项目。存在三个表:rules
、overview
和 relationship
。 relationship
表有两个字段,每个字段都是链接到其他两个表中主键的外键。我在表单中有一个 rules
表的数据表视图,我可以在其中毫无问题地删除记录。但是,当我尝试将记录插入 rules
表中时,该记录将插入到 rules
表中,但没有匹配的记录插入到 关系表。我选中了“强制引用完整性”,以及“级联更新相关字段”和“级联删除相关记录”。我天真地假设这可以处理插入,但显然我错了。所以我现在想知道处理这个问题的最佳方法 - 我是否为表单的
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
通常的方法是使用一个表单将记录插入到基于查询的规则中,该查询包括关系表和允许用户选择相关概述的组合,或者使用以下设置的表单/子表单合适的主/子字段。在 NorthWind 示例数据库中,订单详细信息表是关系表的一个示例,它使用令人厌恶的表内查找反功能,但您可能会得到一些进一步研究的想法。
选项 1 的更详细描述
表格
概述
身份证号
概述
规则
身份证号
规则
关系
RulesID ) 由两个 FK 组成的 PK
OverviewID )
关系
数据
建议 1 查询设计
请注意,关系中的两个字段都包含在查询中。没有必要显示规则中的 ID,因为它是一个自动编号字段,但为了简单起见,将其包含在此处。
如果删除一行,则两个表中的记录都将被删除。
您不能违反引用完整性。您需要先创建所有概述,然后才能工作,或者提供添加概述的不同方法。
如果您更新
RulesID
和OverviewID
,则会出现一条记录添加到关系表,但不添加到规则。如果您更新
OverviewID
和Rule
,记录将被添加到关系和规则中。如果您创建连续表单,则可以以更加用户友好的方式获得上述所有内容更多控制。您可以使用组合框来允许用户选择更友好的概述描述,而不是 ID,并且您可以利用 NotInList 事件来添加新的概述。
请注意,到目前为止,这不需要一行代码。这就是 Access 的力量。
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.
A Much More Detailed Description of Option 1
Tables
Overview
ID
Overview
Rules
ID
Rule
Relationship
RulesID ) PK formed by two FKs
OverviewID )
Relation
Data
Suggestion 1 Query Design
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.
If you update
RulesID
andOverviewID
, a record will be added to the Relationship table, but not to Rules.If you update
OverviewID
andRule
, records will be added to both Relationship and Rules.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.
Note that so far this has not needed a single line of code. That is the power of Access.
如果有问题的键是自动编号 (
IDENTITY
),并且一个表引用另一个表(通过外键),那么您可以创建一个连接两个表的VIEW
,将其插入到视图,并且自动编号值会自动复制到引用表中。这是一个快速演示:If the key in question is an autonumber (
IDENTITY
) and one table references the other (via a foreign key) then you can create aVIEW
joining two tables, insert into the view, and the autonumber value gets automatically copied to the referencing table. Here's a quick demo: