Ms Access 简明教程
MS Access - Many-To-Many Relationship
在本章中，让我们了解多对多关系。要表示多对多关系，您必须创建一个第三张表（通常称为交集表），该表将多对多关系分解为两个一对多关系。为此，我们还需要添加一个交集表。让我们首先添加另一张表 tblAuthers 。
In this chapter, let us understand Many-to-Many Relationship. To represent a many-tomany relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. To do so, we also need to add a junction table. Let us first add another table tblAuthers.
现在让我们创建一个 many-to-many relationship 。我们有多个作者在从事多个项目，反之亦然。如您所知，我们在 tblProjects 中有一个 Author 字段，因此我们为其创建了一个表。我们不再需要此字段。
Let us now create a many-to-many relationship. We have more than one author working on more than one project and vice versa. As you know, we have an Author field in tblProjects so, we have created a table for it. We do not need this field any more.
选择 Author 字段并按删除按钮，您将看到以下消息。
Select the Author field and press the delete button and you will see the following message.
单击 Yes 。现在我们必须创建一个交集表。此交集表包含两个外键，如下面的屏幕截图所示。
Click Yes. We will now have to create a junction table. This junction table have two foreign keys in it as shown in the following screenshot.
这些外键字段将是连接在一起的两个表的主键，即 tblAuthers 和 tblProjects 。
These foreign key fields will be the primary keys from the two tables that were linked together — tblAuthers and tblProjects.
要在 Access 中创建复合键，请选择这两个字段，然后在 table tools design 选项卡中，您可以直接单击该主键，这将标记这两个字段，而不仅仅是一个字段。
To create a composite key in Access, select both these fields and from the table tools design tab, you can click directly on that primary key and that will mark not one but both of these fields.
这两个字段的组合是 tables’ unique identifier 。现在让我们将此表另存为 tblAuthorJunction 。
The combination of these two fields is the tables’ unique identifier. Let us now save this table as tblAuthorJunction.
将多对多关系结合在一起的最后一步是返回到该 relationships view ，然后通过单击 Show Table 来创建这些关系。
The last step in bringing the many-to-many relationships together is to go back to that relationships view and create those relationships by clicking on Show Table.
选择上面突出显示的三个表，然后单击添加按钮，然后关闭此对话框。
Select the above three highlighted tables and click on the Add button and then close this dialog box.
单击并拖动 tblAuthors 中的 AuthorID 字段，然后将其放在 tblAuthorJunction 表 AuthorID 上。
Click and drag the AuthorID field from tblAuthors and place it on top of the tblAuthorJunction table AuthorID.
您正在创建的关系是 Access 会将其视为一对多关系的关系。我们还将强制参照完整性。让我们现在打开 Cascade Update ，然后单击 Create 按钮，如上面的屏幕截图所示。
The relationship you’re creating is the one that Access will consider as a one-to-many relationship. We will also enforce referential integrity. Let us now turn on Cascade Update and click on the Create button as in the above screenshot.
现在让我们按住 ProjectID ，然后直接从 tblAuthorJunction 将其拖放到 ProjectID 上。
Let us now hold the ProjectID, drag and drop it right on top of ProjectID from tblAuthorJunction.
我们将 Enforce Referential Integrity 和 Cascade Update Related Fields 。
We will Enforce Referential Integrity and Cascade Update Related Fields.
以下是多对多关系。
The following are the many-to-many relationships.
