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

by

Troubleshooting Guide: SQL Server Error 4960 — ALTER TABLE SWITCH Check Constraint Mismatch

Why This Error Happens

This error occurs because SQL Server enforces strict metadata parity between source and target tables during a partition switch operation, and a CHECK constraint on the target table does not have a matching NOCHECK (trusted or untrusted but disabled) counterpart on the source table. Specifically, the source table’s constraint is marked as NOCHECK (not enforced on existing data), while the target table expects that same logical constraint to be actively enforced (CHECK). SQL Server refuses the switch because it cannot guarantee that data moving into the target table actually satisfies a constraint that was never validated on the source side.

Step-by-Step Resolution Guide

Step 1: Identify the Mismatched Constraint

  • Review the exact constraint name reported in the Error 4960 message text (between the %.*ls placeholders).
  • Run the following query to inspect constraint status on both tables:
SELECT name, is_disabled, is_not_trusted 
FROM sys.check_constraints 
WHERE parent_object_id IN (OBJECT_ID('SourceTable'), OBJECT_ID('TargetTable'));
  • Confirm which table has the constraint set to NOCHECK (isdisabled = 1) and which has it as CHECK (isdisabled = 0).

Step 2: Align Constraint States on the Source Table

  • Enable and trust the constraint on the source table to match the target table using:
ALTER TABLE SourceTable WITH CHECK CHECK CONSTRAINT ConstraintName;
  • This forces SQL Server to validate existing data and mark the constraint as trusted, resolving the mismatch.
  • If the source table contains large volumes of data, run this during a maintenance window as the validation scan can be I/O intensive.

Step 3: Alternatively, Disable the Constraint on the Target Table

  • If re-enabling checks on the source is not feasible (e.g., due to known bad data), instead relax the target table’s constraint:
ALTER TABLE TargetTable NOCHECK CONSTRAINT ConstraintName;
  • Note this reduces data integrity enforcement on the target table going forward, so only use this as a short-term workaround.

Step 4: Re-run the ALTER TABLE SWITCH Command

  • Execute the original partition switch statement again:
ALTER TABLE SourceTable SWITCH PARTITION x TO TargetTable PARTITION y;
  • Confirm the operation completes without raising Error 4960.

Step 5: Validate Constraint Definitions Match Exactly

  • If the error persists, compare the full constraint expression (not just the name) between source and target using sys.check_constraints.definition to ensure they are logically identical, as SQL Server also requires expression equivalence for switch operations.

Alternative or Verification Steps

  • After applying the fix, query sys.check_constraints on both tables again to confirm is_disabled and is_not_trusted values are identical for the matching constraint pair.
  • As a quick sanity check, run the switch operation inside a transaction (BEGIN TRAN ... ROLLBACK) first in a test/staging environment to confirm it succeeds before committing changes in production.