SQL Server 2019 Express外键创建失败：无匹配主键/候选键

问题场景 #

使用SQL Server Express 2019创建表外键时出现以下错误：

引用表'Dimension.FinanceSummaryAccounts'中没有与外键'FK_FinanceSummaryAccounts_FinanceChartOfAccounts'的引用列列表匹配的主键或候选键。
无法创建约束或索引。请查看先前的错误。

错误出现在脚本最后两行：

CONSTRAINT [FK_FinanceSummaryAccounts_FinanceChartOfAccounts] 
    FOREIGN KEY([SummaryAccountID])
        REFERENCES [Dimension].[FinanceSummaryAccounts]([SummaryAccountID])

完整SQL脚本如下：

-- CREATE SCHEMA [History] AUTHORIZATION [dbo];
-- CREATE SCHEMA [Dimension] AUTHORIZATION [dbo];
-- CREATE SCHEMA [Fact] AUTHORIZATION [dbo];

CREATE TABLE [Dimension].[FinanceSummaryCategories]
(
    [SummaryCategoryID] INT NOT NULL 
         CONSTRAINT [PK_SummaryCategoryID] PRIMARY KEY IDENTITY,
    [SummaryCategorySortID] MONEY NOT NULL DEFAULT 0,
    [SummaryCategory] NVARCHAR(75) NOT NULL, 
    [Note] NVARCHAR(250) NULL, 
    [ModifiedBy] NVARCHAR(250) NULL, 
    [ModifiedOn] DATETIME NULL DEFAULT GETDATE(), 
    [CreatedBy] NVARCHAR(250) NULL, 
    [CreatedOn] DATETIME NULL DEFAULT GETDATE(),     
)
GO

CREATE TABLE [Dimension].[FinanceSummaryAccounts]
(
    [SummaryAccountID] INT NOT NULL IDENTITY(1,1),     
    -- [SummaryAccountID] INT NOT NULL,     
    [SummaryCategoryID] INT NOT NULL,
    [SummaryAccountSortID] MONEY NOT NULL DEFAULT 0, 
    [SummaryAccount] NVARCHAR(75) NOT NULL, 
    [Format] NVARCHAR(500) NOT NULL DEFAULT '#,0',
    [ModifiedBy] NVARCHAR(250) NULL, 
    [ModifiedOn] DATETIME NULL DEFAULT GETDATE(), 
    [CreatedBy] NVARCHAR(250) NULL, 
    [CreatedOn] DATETIME NULL DEFAULT GETDATE()

    CONSTRAINT [PK_SummaryAccountID] 
         PRIMARY KEY NONCLUSTERED ([SummaryAccountID] ASC, [SummaryCategoryID] ASC),
    CONSTRAINT [FK_FinanceSummaryCategories_FinanceSummaryAccounts] 
         FOREIGN KEY([SummaryCategoryID]) 
             REFERENCES [Dimension].[FinanceSummaryCategories]([SummaryCategoryID])
)
GO

CREATE TABLE [Dimension].[FinanceCOASources]
(
    [COASourceID] INT NOT NULL IDENTITY(1,1), 
    [COASourceSortID] MONEY NOT NULL DEFAULT 0,  
    [COASource] NVARCHAR(100) NOT NULL, 
    [ModifiedBy] NVARCHAR(250) NULL, 
    [ModifiedOn] DATETIME NULL DEFAULT GETDATE(), 
    [CreatedBy] NVARCHAR(250) NULL, 
    [CreatedOn] DATETIME NULL DEFAULT GETDATE(), 

    CONSTRAINT [PK_FinanceCOASources] 
        PRIMARY KEY CLUSTERED ([COASourceID] ASC)        
)
GO

CREATE TABLE [Dimension].[FinanceChartOfAccounts]
(
    [AccountID] INT NOT NULL, 
    [COASourceID] INT NOT NULL, 
    [AccountSortID] MONEY NOT NULL, 
    [AccountDescription] NVARCHAR(250) NOT NULL, 
    [SummaryAccountID] INT NOT NULL, 
    [Active] BIT NOT NULL DEFAULT 1, 
    [Header01] INT NOT NULL, 
    [Header02] INT NOT NULL, 
    [Header03] INT NOT NULL, 
    [Header04] INT NOT NULL, 
    [Header05] INT NOT NULL, 
    [ActualOperator] NVARCHAR(50) NOT NULL DEFAULT '*', 
    [ActualNumber] MONEY NOT NULL DEFAULT 1,
    [BudgetOperator] NVARCHAR(50) NOT NULL DEFAULT '*', 
    [BudgetNumber] MONEY NOT NULL DEFAULT 1,
    [ForecastOperator] NVARCHAR(50) NOT NULL DEFAULT '*', 
    [ForecastNumber] MONEY NOT NULL DEFAULT 1,
    [ModifiedBy] NVARCHAR(250) NULL, 
    [ModifiedOn] DATETIME NULL DEFAULT GETDATE(), 
    [CreatedBy] NVARCHAR(250) NULL, 
    [CreatedOn] DATETIME NULL DEFAULT GETDATE(),

    CONSTRAINT [PK_FinanceChartOfAccounts] 
        PRIMARY KEY CLUSTERED ([AccountID] ASC, [SummaryAccountID] ASC, [COASourceID] ASC),
    CONSTRAINT [FK_FinanceCOASources_FinanceChartOfAccounts] 
        FOREIGN KEY([COASourceID])
            REFERENCES [Dimension].[FinanceCOASources]([COASourceID]),
    CONSTRAINT [FK_FinanceSummaryAccounts_FinanceChartOfAccounts]  
        FOREIGN KEY([SummaryAccountID])
            REFERENCES [Dimension].[FinanceSummaryAccounts]([SummaryAccountID])
)
GO

表关系图：

错误原因 #

问题出在Dimension.FinanceSummaryAccounts表的主键定义上：

CONSTRAINT [PK_SummaryAccountID] 
     PRIMARY KEY NONCLUSTERED ([SummaryAccountID] ASC, [SummaryCategoryID] ASC)

这个主键是复合主键，包含SummaryAccountID和SummaryCategoryID两个字段。SQL Server要求外键引用的列必须与引用表的主键/候选键完全匹配——字段数量、顺序、数据类型都要一致，单个字段无法匹配复合主键，因此报错。

解决方法 #

有两种可行的修复方案：

方案1：修改外键，匹配复合主键 #

在FinanceChartOfAccounts表中添加SummaryCategoryID字段，然后将外键改为引用复合主键：

ALTER TABLE [Dimension].[FinanceChartOfAccounts]
ADD [SummaryCategoryID] INT NOT NULL;

ALTER TABLE [Dimension].[FinanceChartOfAccounts]
DROP CONSTRAINT [FK_FinanceSummaryAccounts_FinanceChartOfAccounts];

ALTER TABLE [Dimension].[FinanceChartOfAccounts]
ADD CONSTRAINT [FK_FinanceSummaryAccounts_FinanceChartOfAccounts]  
    FOREIGN KEY([SummaryAccountID], [SummaryCategoryID])
        REFERENCES [Dimension].[FinanceSummaryAccounts]([SummaryAccountID], [SummaryCategoryID]);

如果是在创建表时直接修改，调整后的FinanceChartOfAccounts表定义如下：

CREATE TABLE [Dimension].[FinanceChartOfAccounts]
(
    [AccountID] INT NOT NULL, 
    [COASourceID] INT NOT NULL, 
    [AccountSortID] MONEY NOT NULL, 
    [AccountDescription] NVARCHAR(250) NOT NULL, 
    [SummaryAccountID] INT NOT NULL, 
    [SummaryCategoryID] INT NOT NULL, -- 添加关联字段
    [Active] BIT NOT NULL DEFAULT 1, 
    [Header01] INT NOT NULL, 
    [Header02] INT NOT NULL, 
    [Header03] INT NOT NULL, 
    [Header04] INT NOT NULL, 
    [Header05] INT NOT NULL, 
    [ActualOperator] NVARCHAR(50) NOT NULL DEFAULT '*', 
    [ActualNumber] MONEY NOT NULL DEFAULT 1,
    [BudgetOperator] NVARCHAR(50) NOT NULL DEFAULT '*', 
    [BudgetNumber] MONEY NOT NULL DEFAULT 1,
    [ForecastOperator] NVARCHAR(50) NOT NULL DEFAULT '*', 
    [ForecastNumber] MONEY NOT NULL DEFAULT 1,
    [ModifiedBy] NVARCHAR(250) NULL, 
    [ModifiedOn] DATETIME NULL DEFAULT GETDATE(), 
    [CreatedBy] NVARCHAR(250) NULL, 
    [CreatedOn] DATETIME NULL DEFAULT GETDATE(),

    CONSTRAINT [PK_FinanceChartOfAccounts] 
        PRIMARY KEY CLUSTERED ([AccountID] ASC, [SummaryAccountID] ASC, [COASourceID] ASC),
    CONSTRAINT [FK_FinanceCOASources_FinanceChartOfAccounts] 
        FOREIGN KEY([COASourceID])
            REFERENCES [Dimension].[FinanceCOASources]([COASourceID]),
    CONSTRAINT [FK_FinanceSummaryAccounts_FinanceChartOfAccounts]  
        FOREIGN KEY([SummaryAccountID], [SummaryCategoryID])
            REFERENCES [Dimension].[FinanceSummaryAccounts]([SummaryAccountID], [SummaryCategoryID])
)
GO

方案2：修改FinanceSummaryAccounts表的主键，改为单字段主键 #

如果业务逻辑上SummaryAccountID本身可以唯一标识记录，不需要复合主键，可以调整主键定义：

ALTER TABLE [Dimension].[FinanceSummaryAccounts]
DROP CONSTRAINT [PK_SummaryAccountID];

ALTER TABLE [Dimension].[FinanceSummaryAccounts]
ADD CONSTRAINT [PK_SummaryAccountID] 
    PRIMARY KEY NONCLUSTERED ([SummaryAccountID] ASC);

这样原有的外键定义就可以正常生效，不需要修改FinanceChartOfAccounts表。

注意：修改主键前要确保SummaryAccountID在表中是唯一的，避免出现重复值导致主键创建失败。

内容的提问来源于stack exchange，提问作者Codernator