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

by

Troubleshooting Guide: SQL Server Error 1989 — Cannot Enable Foreign Key Constraint Due to Disabled Index

Why This Error Happens

This error occurs because the index supporting the referenced key (typically the primary key or unique constraint on the parent table) has been disabled using an ALTER INDEX ... DISABLE statement. SQL Server requires that all indexes backing a referenced key be active and rebuilt before any foreign key constraint pointing to that key can be enabled or re-enabled. This commonly happens after maintenance operations, bulk load routines, or migration scripts that disable indexes for performance reasons but fail to re-enable them afterward.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Index

  • Run the following query to locate disabled indexes on the referenced table:
  SELECT name, is_disabled, object_id 
  FROM sys.indexes 
  WHERE is_disabled = 1;
  • Cross-reference the object_id with sys.objects to confirm the table name matches the one referenced in the error message.
  • Note the exact index name reported in Error 1989 ('%.*ls' placeholder will show the real index name in your error log).

Step 2: Rebuild the Disabled Index

  • Execute a full index rebuild to reactivate it:
  ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;
  • If multiple indexes are disabled, rebuild all of them using:
  ALTER INDEX ALL ON [SchemaName].[TableName] REBUILD;
  • On Windows Server 2025, ensure the SQL Server service account has sufficient disk space in the default data directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) since rebuilds require temporary storage.

Step 3: Re-Enable the Foreign Key Constraint

  • Once the index is active, attempt to re-enable the constraint:
  ALTER TABLE [SchemaName].[ChildTable] 
  WITH CHECK CHECK CONSTRAINT [ForeignKeyName];
  • Confirm no orphaned rows exist in the child table that violate the relationship, as this will cause a separate validation error (547) after the index issue is resolved.

Step 4: Review Maintenance Scripts and Jobs

  • Check SQL Server Agent Jobs or external ETL/ELT scripts (e.g., SSIS packages, PowerShell automation) that may be disabling indexes as part of a bulk-load routine.
  • Add explicit REBUILD steps immediately after disable operations to prevent this issue from recurring in future maintenance windows.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes again to confirm is_disabled = 0 for the affected index, and check sys.foreign_keys to ensure is_disabled = 0 and is_not_trusted = 0 for the constraint.
  • If rebuilding the index is not immediately feasible due to table size or locking concerns, consider using ALTER INDEX ... REBUILD WITH (ONLINE = ON) (Enterprise Edition only) to minimize downtime while restoring index availability.