Resolving “There Are No Primary or Candidate Keys” When Mapping Composite Foreign Keys in SQL Server

When working with relational database designs, it’s common to establish relationships between tables using foreign keys. However, issues arise when a table uses a composite primary key — a key composed of multiple columns — and you’re trying to create a foreign key in a child table that references it. One of the most confusing errors you might encounter is:

There are no primary or candidate keys in the referenced table

This article explains why this happens and shows how to properly map a composite primary key in a foreign key relationship using SQL Server.

The Problem Scenario

Let’s consider a scenario where you have a main table (securities_accounts) with a composite primary key:

CREATE TABLE securities_accounts (
    consent_id VARCHAR(255),
    resource_id VARCHAR(255),
    -- other columns...
    PRIMARY KEY (consent_id, resource_id)
);

Now, you want to create a child table (securities_account_fee) that maps to this table in a one-to-many relationship, using both consent_id and resource_id as foreign keys.

However, the following incorrect approach results in an error:

-- ❌ INCORRECT: Only maps one part of the composite key
CREATE TABLE securities_account_fee (
    id BIGINT PRIMARY KEY,
    fk_consent_id VARCHAR(255),
    fk_resource_id VARCHAR(255),
    FOREIGN KEY (fk_consent_id) REFERENCES securities_accounts(consent_id)
);

Resulting Error:

There are no primary or candidate keys in the referenced table ‘securities_accounts’ that match the referencing column list in the foreign key.

Why the Error Occurs

This happens because:

• securities_accounts has a composite key (consent_id, resource_id)
• Referencing only one of those columns (consent_id) is not enough to uniquely identify a row
• SQL Server requires you to reference all columns in the composite key to create a valid foreign key relationship

The Correct Solution

You need to include both parts of the composite key in your child table and reference them together:

-- ✅ CORRECT: Composite foreign key matches composite primary key
CREATE TABLE securities_account_fee (
    id BIGINT PRIMARY KEY,
    fk_consent_id VARCHAR(255),
    fk_resource_id VARCHAR(255),
    FOREIGN KEY (fk_consent_id, fk_resource_id)
        REFERENCES securities_accounts(consent_id, resource_id)
);

This successfully creates a valid foreign key constraint, ensuring referential integrity.

Additional Best Practices

1. ✅ Ensure Matching Data Types and Lengths
   The columns in the foreign key must match the primary key columns in type, size, and order.
2. ✅ Indexing Foreign Keys
   For performance reasons, consider indexing foreign key columns in large child tables.
3. ✅ Use Descriptive Constraint Names
   Name your foreign key constraints clearly:

   CONSTRAINT FK_fee_to_account FOREIGN KEY (...)
   

4. ✅ Validate Relationships Early
   Always test foreign key mappings before inserting data, especially in composite key scenarios.

Common Use Cases for Composite Keys

• Junction tables in many-to-many relationships
• Tables storing account details across multiple institutions
• Audit tables tracking changes across compound identifiers (e.g., user ID + session ID)

Conclusion

When designing relational databases with composite primary keys, always ensure that foreign key constraints include all components of the composite key. Otherwise, SQL Server will not allow the relationship and will throw the dreaded “There are no primary or candidate keys…” error.

By properly aligning the child table’s foreign key with the parent table’s composite primary key, you ensure data consistency, avoid logical errors, and create robust database schemas.