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

问题描述

这是一个小型的数据库结构，我创建的只是为了向您显示关系我想模型。基本上这很简单，每年有12个时期，一个时期的情况不能出现两次（2012年9个时期不能出现超过一次）。 b
$ b

所以我认为最好这个模型的方法是建立一个只有一个字段值为1-12的表格周期，一个表格年份遵循相同的逻辑（2011,2012 ...），并且由于它是一个N对N的关系创建了period_by_year表，将其连接到rpt_maintenance_kpi使用的表。现在棘手的部分是，为了使每个组合独一无二，我已经做出了复合主键的period_no和year_no部分。这在我看来优雅地解决了这个问题，但是我坚持如何从rpt_maintenance_kpi（或任何其他表）引用这个复合主键。我已经尝试了两个连接，但是这似乎不工作（创建第二个rpt_maintenance_kpi表，我相信这不会做我想做的事情）。

我可以处理一个外键到一个复合主键？

非常感谢您提前。

解决方案使用维护创建年或期间关系，然后双击关系线编辑关系，或者将第二部分（年份或期间相应地）拖动到维护，并在询问您是否想要时编辑关系。您现在可以添加第二行，如下所示：

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

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).

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 ?

Many thanks in advance.

解决方案

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屋！