No primary or candidate keys in the table of reference that match the referencing column list in the foreign key

Question

I'm using SQL Server Express 2019. I gett an error every time I try to create a foreign key relationship with a table.

There are no primary or candidate keys in the referenced table 'Dimension.FinanceSummaryAccounts' that match the referencing column list in the foreign key 'FK_FinanceSummaryAccounts_FinanceChartOfAccounts'.
Could not create constraint or index. See previous errors

The error is appearing on the last 2 lines of the SQL script.

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

Why this error?

Here is my SQL script and a diagram of the tables and the error.

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

Answer 1

Error seems clear: there is no primary key on just FinanceSummaryAccounts(SummaryAccountID), so you can't create a foreign key that points only to that column.

Consider the case where you have two rows like this in FinanceSummaryAccounts:

SummaryAccountID | SummaryCategoryID
----------------   -----------------
               5 |                12
               5 |                27

Now if you add a row to FinanceChartOfAccounts with a SummaryAccountID of 5, which of the above two rows does it reference? It can only reference one.

A primary key that consists of two columns can only be referenced by foreign keys that also consist of two columns. So the solution depends on the actual requirement and data model, which we can't possibly know (other than it seems weird to have an account fit in two categories):

1. If the Accounts table can have two rows with the same SummaryAccountID and a different SummaryCategoryID, then the foreign key table also needs to have both columns so that you can reference exactly one parent row.

2. If the accounts table can only have a single row for any given SummaryAccountID, then SummaryCategoryID simply shouldn't be part of the primary key, and then the single-column foreign key will work.

3. I suppose the third solution could be that the foreign key should actually point at a table not represented on the diagram that has a primary key or unique constraint on only the SummaryAccountID column.