When working with Microsoft SQL Server (MSSQL), one of the most common issues developers encounter while creating foreign key constraints is:

Msg 1776, Level 16
There are no primary or candidate keys in the referenced table 
that match the referencing column list in the foreign key.

This error typically appears together with:

Msg 1750, Level 16
Could not create constraint or index. See previous errors.

If you’re building relational integrity between tables and hit this problem, this guide will explain:

• Why the error occurs
• What SQL Server requires for foreign keys
• How to diagnose the issue
• Safe ways to fix it
• Best practices for schema design

Why SQL Server Throws Error 1776

In SQL Server, a foreign key must reference a column (or set of columns) that is either:

• A PRIMARY KEY
• A UNIQUE constraint
• A column with a UNIQUE index

If the referenced column is just a regular column without uniqueness enforcement, SQL Server cannot guarantee referential integrity — therefore it refuses to create the foreign key.

Example Scenario

You attempt to create:

CONSTRAINT fk_securities_order 
FOREIGN KEY (securities_order_id)
REFERENCES dbo.securities_orders(id)

But the column id in securities_orders is not defined as PRIMARY KEY or UNIQUE.

SQL Server then correctly raises:

There are no primary or candidate keys in the referenced table…

What SQL Server Means by “Primary or Candidate Keys”

• Primary Key → The main unique identifier of a table.
• Candidate Key → Any column (or combination of columns) that has a UNIQUE constraint and could serve as a primary key.

If the referenced column is not unique, SQL Server cannot ensure that:

• One foreign key row points to exactly one parent row.
• Duplicate parent values don’t create ambiguity.

How to Diagnose the Problem

To check whether the referenced column has a PRIMARY or UNIQUE constraint:

SELECT 
    KCU.TABLE_NAME,
    KCU.COLUMN_NAME,
    TC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE 
    TC.TABLE_NAME = 'securities_orders'
    AND TC.TABLE_SCHEMA = 'dbo'
    AND TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE');

If your referenced column does not appear here, that’s your problem.

How to Fix the Error

✅ Option 1: Add a Primary Key

If the column should uniquely identify each row, make it a primary key:

ALTER TABLE dbo.securities_orders
ADD CONSTRAINT PK_securities_orders 
PRIMARY KEY (id);

⚠️ Only do this if:

• The column truly contains unique values.
• There are no duplicates in the data.

Check first:

SELECT id, COUNT(*)
FROM dbo.securities_orders
GROUP BY id
HAVING COUNT(*) > 1;

✅ Option 2: Add a UNIQUE Constraint

If the column should be unique but is not the main identifier:

ALTER TABLE dbo.securities_orders
ADD CONSTRAINT UQ_securities_orders_id 
UNIQUE (id);

This allows foreign keys without making it the primary key.

✅ Option 3: Reference the Actual Primary Key

Sometimes the real issue is that you’re referencing the wrong column.

Check the actual primary key:

EXEC sp_help 'dbo.securities_orders';

Then reference the correct PK column.

❌ What NOT to Do

• Do not disable constraints just to “make it work”
• Do not remove relational integrity requirements
• Do not create foreign keys to non-unique columns

That defeats the purpose of relational database design.

Important Design Consideration: Composite Keys

If the relationship requires two columns together (for example id + sec_account_resource_id), then:

1. The parent table must have a composite PRIMARY KEY or UNIQUE constraint.
2. The child table must reference both columns together.

Example:

Parent Table

ALTER TABLE dbo.securities_orders
ADD CONSTRAINT UQ_securities_orders_composite
UNIQUE (id, sec_account_resource_id);

Child Table

CREATE TABLE sec_orders_types_of_order (
    securities_order_id VARCHAR(255) NOT NULL,
    s_a_resource_id VARCHAR(255) NOT NULL,
    types_of_order VARCHAR(255),
    CONSTRAINT fk_securities_order 
        FOREIGN KEY (securities_order_id, s_a_resource_id)
        REFERENCES dbo.securities_orders(id, sec_account_resource_id)
);

Foreign keys must match the exact column structure of the referenced key.

Why SQL Server Enforces This Strictly

SQL Server enforces this rule because:

• Referential integrity guarantees data consistency.
• Query optimization relies on uniqueness.
• Index structures depend on deterministic relationships.

Allowing foreign keys to non-unique columns would break relational guarantees.

Best Practices for MSSQL Foreign Key Design

1️⃣ Always define primary keys when creating tables

CREATE TABLE example (
    id INT NOT NULL PRIMARY KEY,
    ...
);

2️⃣ Keep key data types identical

Foreign key column type must match exactly:

• Same data type
• Same length
• Same collation (for strings)

3️⃣ Avoid oversized VARCHAR(255) keys

Prefer:

• INT
• BIGINT
• UNIQUEIDENTIFIER

String keys increase index size and reduce performance.

4️⃣ Design relationships before populating large datasets

Changing keys later can require table rebuilds.

Summary

Error 1776 in SQL Server occurs because:

A foreign key must reference a PRIMARY KEY or UNIQUE column.

To fix it:

1. Verify the referenced column is unique.
2. Add a PRIMARY KEY or UNIQUE constraint if appropriate.
3. Ensure data types match.
4. Consider composite keys if referencing multiple columns.

Understanding this principle improves both your database reliability and schema quality.