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

by

Troubleshooting Guide: SQL Server Error 3802 — Constraint May Be Impacted by Collation Upgrade

Why This Error Happens

This warning occurs when SQL Server 2025 performs a collation upgrade (often during an in-place upgrade, database restore, or ALTER DATABASE COLLATE operation) and detects CHECK or FOREIGN KEY constraints that reference character-based columns. Because the collation change can alter sort order and comparison rules, SQL Server cannot guarantee that existing data still satisfies the constraint logic under the new collation, so it flags the constraint as untrusted rather than silently assuming validity. This is not data corruption — it is a safety flag requiring manual revalidation.

Step-by-Step Resolution Guide

Step 1: Identify All Impacted Constraints

  • Run the following query to list all untrusted constraints in the affected database:
  • SELECT name, OBJECT_NAME(parent_object_id) AS TableName FROM sys.check_constraints WHERE is_not_trusted = 1 UNION ALL SELECT name, OBJECT_NAME(parent_object_id) FROM sys.foreign_keys WHERE is_not_trusted = 1;
  • Cross-reference this list with the exact table/constraint names printed in the Error 3802 messages from the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).

Step 2: Disable and Re-Enable Each Constraint WITH CHECK

  • For CHECK constraints, run: ALTER TABLE dbo.YourTable NOCHECK CONSTRAINT YourConstraintName; followed immediately by ALTER TABLE dbo.YourTable WITH CHECK CHECK CONSTRAINT YourConstraintName;
  • For FOREIGN KEY constraints, use the same syntax pattern substituting the FK constraint name.
  • The WITH CHECK clause forces SQL Server to re-scan existing data against the new collation rules and marks the constraint as trusted again if no violations are found.

Step 3: Automate for Bulk Constraints (Multiple Tables)

  • If dozens of constraints are impacted, generate dynamic SQL rather than fixing them manually:
  • SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(name) + ';' FROM sys.check_constraints WHERE is_not_trusted = 1;
  • Copy the generated output into a new query window and execute it as a batch.

Step 4: Check for Underlying Data Violations

  • If the WITH CHECK CHECK CONSTRAINT step fails with error 547, the data itself violates the constraint under the new collation (e.g., duplicate values that now compare as equal, or string comparisons that now evaluate differently).
  • Query the offending rows manually using the constraint’s CHECK expression to isolate and clean bad data before re-attempting Step 2.

Step 5: Rebuild Indexes on Affected Columns

  • Collation changes can also affect index key ordering on char/varchar/nchar/nvarchar columns.
  • Run ALTER INDEX ALL ON dbo.YourTable REBUILD; on each impacted table to ensure index structures align with the new collation sort order.

Alternative or Verification Steps

  • Verify the fix by re-running the Step 1 query — if it returns zero rows, all constraints are trusted and the collation upgrade is fully reconciled.
  • If constraints repeatedly fail validation due to large-scale data conflicts, consider dropping and recreating the constraint instead of using NOCHECK/WITH CHECK, after first cleaning the conflicting data using a staging table comparison.