Access 2007 中对复合主键的引用 [英] Reference to composite primary key in Access 2007

问题描述

我环顾四周，发现了一些类似的问题，但它们是针对 SQL Server 的.

I have looked around and found some questions similar but they were for SQL Server instead.

这是我创建的一个小型数据库，旨在向您展示我想要建模的关系.基本上很简单，每年有 12 个时期，一个时期的实例不能出现两次(时期 9 年 2012 不能出现超过一次).

Here is a small database structured I have create just to show you the relationships I want to model. Basically it's quite simple, each year has 12 periods and an instance of period-year cannot occur twice (period 9 year 2012 cannot occur more than once ever).

所以我认为最好的建模方法是有一个表格周期，其中只有一个字段的值从 1 到 12，表格年份遵循相同的逻辑(2011、2012...)，因为它是N 对 N 关系我创建了 period_by_year 表，将它们连接起来供 rpt_maintenance_kpi 使用.现在棘手的部分是，为了使每个组合唯一，我将 period_no 和 year_no 都设置为复合主键.在我看来，这很好地解决了这个问题，但是我被困在如何从 rpt_maintenance_kpi(或任何其他表)中引用这个复合主键.我尝试进行两次连接，但这似乎不起作用(创建第二个 rpt_maintenance_kpi 表，我相信这不会做我想做的事).

So I thought that the best way to model this would be to have a table period with only one field with values from 1-12, a table year following the same logic (2011,2012...) and since it is an N-to-N relationship I have created the period_by_year table which joins them to be used by rpt_maintenance_kpi. Now the tricky part is that in order to make each combination unique, I have made the both period_no and year_no part of a composite primary key. This solves the problem elegantly in my opinion, but then I am stuck on how to reference this composite primary key from the rpt_maintenance_kpi (or any other table for that matter). I have tried making two joins but this does not seem to work (creates a second rpt_maintenance_kpi table and I believe this will not do what I want to do).

那么我该如何处理复合主键的外键呢?

So how could I handle a foreign key to a composite primary key ?

非常感谢.

推荐答案

使用Maintenance 创建Year or Period 关系，然后双击关系行编辑关系或将第二部分(相应的年份或期间)拖到维护并在询问您是否要编辑关系时选择是.您现在可以添加第二行，如下所示:

Create the Year or Period relationship with Maintenance, then either double-click the relationship line to edit the relationship or drag the second part (year or period accordingly) to Maintenance and choose Yes when asked if you want to edit the relationship. You can now add the second line, like so:

这篇关于Access 2007 中对复合主键的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！