access数据违反参照完整_如何处理Access中插入的参照完整性

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?

解决方案

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 and OverviewID, a record will be added to the Relationship table, but not to Rules.

If you update OverviewID and Rule, 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.