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

by

Troubleshooting Guide: SQL Server Error 1930 – Cannot Convert Nonclustered Index to Clustered Index

Why This Error Happens

This error occurs because SQL Server enforces referential integrity by requiring that indexes referenced by a foreign key constraint remain structurally unchanged during certain ALTER operations. When you attempt to convert a nonclustered index to a clustered index using CREATE INDEX ... WITH DROP_EXISTING, SQL Server must effectively drop and rebuild the underlying index structure, which conflicts with any active foreign key constraint that depends on it. SQL Server 2025 blocks this operation outright rather than risk breaking the constraint relationship, forcing you to manually remove the dependency first.

Step-by-Step Resolution Guide

Step 1: Identify the Foreign Key Constraint Blocking the Operation

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
  • Run the following query to find dependent foreign keys on the target table:
  • SELECT fk.name AS ForeignKeyName, OBJECT_NAME(fk.parent_object_id) AS TableName FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('YourTableName');
  • Note down the exact constraint name(s) returned, as you will need them in the next step.

Step 2: Drop the Foreign Key Constraint Temporarily

  • Execute an ALTER TABLE statement to remove the constraint:
  • ALTER TABLE YourChildTable DROP CONSTRAINT FK_ConstraintName;
  • Confirm no active transactions are locking the table using sp_who2 or Activity Monitor before proceeding.

Step 3: Convert the Index to Clustered

  • Run the index conversion command now that the dependency is cleared:
  • CREATE UNIQUE CLUSTERED INDEX IX_YourIndexName ON YourTableName(YourColumn) WITH (DROP_EXISTING = ON);
  • Verify the operation completes without Error 1930 appearing in the Messages pane.

Step 4: Recreate the Foreign Key Constraint

  • Re-add the foreign key you dropped in Step 2, pointing it back to the newly clustered index:
  • ALTER TABLE YourChildTable ADD CONSTRAINT FK_ConstraintName FOREIGN KEY (YourColumn) REFERENCES YourTableName(YourColumn);
  • Check the constraint status in sys.foreign_keys to ensure is_disabled = 0.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes and confirming type_desc = 'CLUSTERED' for the target index, and re-check sys.foreign_keys to ensure the constraint relationship is restored.
  • If dropping the constraint is not feasible (e.g., production environment with strict uptime requirements), consider creating the clustered index on a different unique column that has no foreign key dependency instead.