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

by

Troubleshooting Guide: SQL Server Error 1779 — “Table Already Has a Primary Key Defined”

Why This Error Happens

This error occurs when a CREATE TABLE or ALTER TABLE statement attempts to define a second primary key constraint on a table that already has one, since SQL Server enforces a strict one-primary-key-per-table rule at the schema level. It commonly surfaces during schema migration scripts, ORM-generated DDL (Entity Framework, Hibernate), or replicated/synchronized deployment scripts that are run more than once without idempotency checks. In some cases, this is also triggered by conflicting schema versions when multiple deployment pipelines or DACPAC packages attempt to apply overlapping table definitions.

Step-by-Step Resolution Guide

Step 1: Identify the Existing Primary Key Constraint

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance.
  • Run the following query to inspect existing constraints on the affected table:
SELECT name, type_desc 
FROM sys.key_constraints 
WHERE parent_object_id = OBJECT_ID('YourSchema.YourTableName') 
AND type = 'PK';
  • Note the constraint name returned (e.g., PK_YourTableName), as you will need it in the next step.

Step 2: Drop or Modify the Conflicting Constraint

  • If the script is attempting to re-add an identical key, drop the existing constraint first using:
ALTER TABLE YourSchema.YourTableName 
DROP CONSTRAINT PK_YourTableName;
  • If you intend to change the primary key column(s), drop the old constraint before adding the new one in the same transaction block to avoid downtime.
  • On Windows Server 2025 environments using SQL Server Configuration Manager, ensure no concurrent replication jobs (under SQL Server Agent > Jobs) are re-triggering the same DDL script simultaneously.

Step 3: Audit and Fix the Deployment Script

  • Review your migration or DACPAC script for a missing existence check before the ADD CONSTRAINT statement.
  • Wrap the constraint creation logic in a conditional check to prevent future re-execution errors:
IF NOT EXISTS (
    SELECT 1 FROM sys.key_constraints 
    WHERE parent_object_id = OBJECT_ID('YourSchema.YourTableName') AND type = 'PK'
)
BEGIN
    ALTER TABLE YourSchema.YourTableName 
    ADD CONSTRAINT PK_YourTableName PRIMARY KEY (YourColumnName);
END
  • Save and re-run the corrected script via SQLCMD or SSMS.

Step 4: Clear Cached Deployment Artifacts (If Using CI/CD)

  • If using Azure DevOps or GitHub Actions pipelines on Windows Server 2025 to deploy DACPACs, clear the local build cache located typically at C:\ProgramData\SqlPackage\Cache or the pipeline’s temp artifact folder.
  • Re-publish the DACPAC with the /p:BlockOnPossibleDataLoss=false flag only if you have confirmed the schema change is safe.

Alternative or Verification Steps

  • Verify the fix by re-running the original deployment script; Error 1779 should no longer appear, and querying sys.key_constraints should show exactly one PK type entry for the table.
  • As an alternative, if constraint names are inconsistent across environments, generate a fresh schema comparison report using SQL Server Data Tools (SSDT) to identify all duplicate key definitions before re-deploying.