复合主键+外键 [英] Composite Primary Key + Foreign Key

问题描述

我有一个表，其中包含调查列表（PK为ID）

I have a table which contains a list of Surveys (PK is ID)

CREATE TABLE [dbo].[SurveyMaster](
    [ID] [nvarchar](4) NOT NULL,
    [Title] [nvarchar](200) NULL,
    [IsActive] [bit] NOT NULL,

还有一个包含变量映射列表的表。

And a table which contains a list of Variable Mappings.

CREATE TABLE [dbo].[VariableMappings](
    [ParentSurvey_ID] [nvarchar](4) NOT NULL,
    [ReportingMonth] [nvarchar](6) NOT NULL,
    [VariableName] [nvarchar](400) NOT NULL,
    [Value] [int] NOT NULL

我的希望是在ParentSurvey_ID，ReportingMonth和Variable Name上为VariableMappings创建一个主键，以确保唯一记录。

My hope was to create a primary key for VariableMappings on ParentSurvey_ID, ReportingMonth and Variable Name to ensure a unique record.

不过，我希望在VariableMappings.ParentSurvey_ID和SurveyMaster.ID之间建立外键关系，以确保VariableMappings仅包含相关的SurveyID。

At the same time though, I'd like a foreign key relationship between VariableMappings.ParentSurvey_ID and SurveyMaster.ID to ensure VariableMappings only contains relevant SurveyID's.

我尝试了几种方法在SQL Server中，但由于组合键由3列组成，我不相信可以创建FK。

I've tried a couple of approaches in SQL Server but I don't believe I can create the FK due to the composite key being made up of 3 columns.

我该如何实现这一目标？

How can I go about achieving this?

推荐答案

是的，您可以：

CREATE TABLE [dbo].[VariableMappings](
    [ParentSurvey_ID] [nvarchar](4) NOT NULL,
    [ReportingMonth] [nvarchar](6) NOT NULL,
    [VariableName] [nvarchar](400) NOT NULL,
    [Value] [int] NOT NULL,
  PRIMARY KEY (ParentSurvey_ID, ReportingMonth, VariableName),
  FOREIGN KEY (ParentSurvey_ID)
    REFERENCES dbo.SurveyMaster (ID)
) ;

---

大多数与您的问题无关，但具有 PRIMARY KEY 这么宽（410个nvarchars）不是最好的主意。

---

Mostly irrelevant to your problem, but having a PRIMARY KEY that is so wide (410 nvarchars) is not the best idea.

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