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

by

Troubleshooting Guide: SQL Server Error 1992 – Foreign Key Disabled Due to Index Disable

Why This Error Happens

This warning occurs when a clustered or unique index supporting a foreign key constraint is explicitly disabled using an ALTER INDEX ... DISABLE statement. Since SQL Server relies on the underlying index to validate and enforce referential integrity, disabling that index leaves the foreign key without a mechanism to check key values, forcing SQL Server to disable the constraint as well. This is not caused by corruption or permissions, but is an expected cascading side effect of index maintenance operations, rebuild scripts, or administrative scripts that disable indexes without rebuilding them immediately afterward.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Index and Affected Foreign Key

  • Run the following query to locate disabled indexes in the database:
  SELECT name, object_id, is_disabled 
  FROM sys.indexes 
  WHERE is_disabled = 1;
  • Cross-reference the table name from the error message with sys.foreign_keys to confirm which constraint was disabled:
  SELECT name, is_disabled, referenced_object_id 
  FROM sys.foreign_keys 
  WHERE is_disabled = 1;

Step 2: Rebuild the Disabled Index

  • Use ALTER INDEX ... REBUILD to restore the index, which is required before the foreign key can be re-enabled:
  ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;
  • If the index was part of a maintenance plan or script, check SQL Server Management Studio (SSMS) > Maintenance Plans on Windows Server 2025 to ensure the disable/rebuild steps are sequenced correctly.

Step 3: Re-Enable the Foreign Key Constraint

  • Once the index is rebuilt, re-enable the foreign key with validation using:
  ALTER TABLE [SchemaName].[TableName] 
  WITH CHECK CHECK CONSTRAINT [ForeignKeyName];
  • Using WITH CHECK ensures existing data is validated against the constraint, preventing silent data integrity issues.

Step 4: Review Maintenance Scripts and Jobs

  • Open SQL Server Agent > Jobs in SSMS or use Task Scheduler on Windows Server 2025 to review any automated jobs that disable indexes.
  • Modify scripts to include an immediate REBUILD step rather than leaving indexes in a disabled state for extended periods.

Alternative or Verification Steps

  • Verify the fix by querying sys.foreign_keys again and confirming is_disabled = 0 for the affected constraint.
  • If re-enabling fails due to data inconsistencies, run DBCC CHECKCONSTRAINTS('TableName') to identify and correct orphaned rows before retrying the ALTER TABLE command.