How to fix Error 1773 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 1773 – Implicit Foreign Key Reference Without Primary Key

Why This Error Happens

This error occurs when you attempt to create a foreign key constraint that references a table which does not have a primary key or a unique constraint defined on the referenced column(s). SQL Server 2025 requires that any column referenced by a foreign key relationship must be uniquely identifiable through either a primary key or a unique index/constraint. This typically happens during schema migrations, automated deployment scripts, or when a table was created without proper key constraints before relationships were established.

Step-by-Step Resolution Guide

Step 1: Identify the Referenced Table and Column

  • Open SQL Server Management Studio (SSMS) 21.x or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run the following query to extract the exact object names from the error message:
  SELECT OBJECT_NAME(parent_object_id) AS ReferencingTable,
         OBJECT_NAME(referenced_object_id) AS ReferencedTable
  FROM sys.foreign_keys
  WHERE name = 'YourForeignKeyName';
  • Confirm whether the referenced table currently has a PRIMARY KEY or UNIQUE constraint using:
  EXEC sp_helpconstraint 'ReferencedTableName';

Step 2: Add a Primary Key or Unique Constraint

  • Navigate to the referenced table in SSMS Object Explorer (Databases > YourDB > Tables > dbo.ReferencedTable).
  • If no primary key exists, add one via T-SQL:
  ALTER TABLE dbo.ReferencedTable
  ADD CONSTRAINT PK_ReferencedTable PRIMARY KEY (ColumnName);
  • If the column contains duplicate values (common cause of failure), first deduplicate the data using a ROW_NUMBER() cleanup script before applying the constraint.

Step 3: Recreate the Foreign Key Constraint

  • Once the primary key exists, re-run the original foreign key creation script:
  ALTER TABLE dbo.ReferencingTable
  ADD CONSTRAINT FK_YourConstraintName
  FOREIGN KEY (ColumnName) REFERENCES dbo.ReferencedTable(ColumnName);
  • Ensure the data types of both the foreign key column and primary key column match exactly (e.g., both INT or both BIGINT).

Step 4: Validate Server-Level Configuration (Windows Server 2025/Windows 11)

  • Open SQL Server Configuration Manager and confirm the SQL Server service is running under an account with sufficient schema-modification permissions (db_ddladmin or sysadmin).
  • Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for any related schema lock or permission denial entries that might be blocking the ALTER TABLE operation.

Alternative or Verification Steps

  • Verify the fix by querying sys.foreign_keys and sys.key_constraints to confirm both the primary key and foreign key now exist and are enabled (is_disabled = 0).
  • If the table legitimately cannot have a primary key (e.g., a staging/heap table), create a UNIQUE nonclustered index on the referenced column as an alternative to satisfy the foreign key requirement.