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

by

Troubleshooting Guide: Error 1754 — “Defaults Cannot Be Created on Columns with an IDENTITY Attribute”

Why This Error Happens

This error occurs because SQL Server does not allow a DEFAULT constraint to be bound to a column that already has the IDENTITY property, since both features compete to auto-populate column values on insert. It typically surfaces when a T-SQL script, ORM migration (like Entity Framework), or a third-party schema tool attempts to run ALTER TABLE ... ADD CONSTRAINT DF_... DEFAULT against an identity column. It can also happen during table replication, import/export operations, or when a generated script from SSMS incorrectly reapplies defaults to columns flagged as IDENTITY(1,1).

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Script

  • Open the full error message in SSMS or the SQL Server error log to extract the table name referenced in %.*ls.
  • Run the following query to confirm which column has the IDENTITY property:
  SELECT name, is_identity 
  FROM sys.columns 
  WHERE object_id = OBJECT_ID('YourTableName') AND is_identity = 1;
  • Locate the migration script, deployment pipeline, or application code (e.g., Entity Framework Core migration file) that is issuing the ADD DEFAULT statement.

Step 2: Remove or Correct the Default Constraint Definition

  • Edit the script to eliminate the DEFAULT clause on the identity column entirely, since IDENTITY already handles value generation.
  • If using an ORM, check model annotations (e.g., [DatabaseGenerated(DatabaseGeneratedOption.Identity)] in EF Core) and ensure no conflicting HasDefaultValue() or HasDefaultValueSql() call exists for the same property.
  • If the script was auto-generated via SSMS “Script Table as > CREATE To”, manually strip out the erroneous CONSTRAINT [DF_TableName_ColumnName] DEFAULT line before execution.

Step 3: Recreate the Table or Column Correctly (If Already Broken)

  • If the table was partially created and is now in a broken state, drop and recreate it without the conflicting default:
  CREATE TABLE dbo.YourTableName (
      Id INT IDENTITY(1,1) PRIMARY KEY,
      OtherColumn NVARCHAR(50) NULL
  );
  • Reapply any needed default values only to non-identity columns using standard DEFAULT syntax.

Step 4: Validate SQL Server 2025 Compatibility Settings

  • Confirm the database compatibility level is correctly set for SQL Server 2025 (170) via:
  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabase';
  • Update if necessary:
  ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 170;
  • This ensures no legacy behavior changes from older compatibility levels are causing unexpected constraint handling.

Alternative or Verification Steps

  • Verify the fix by re-running the original migration/script; the table should create successfully without Error 1754, and sys.default_constraints should show no entries tied to the identity column.
  • As an alternative, if default values are genuinely needed for display purposes, handle them at the application layer or use a computed column instead of a DEFAULT constraint on the identity field.