Cannot drop index because of foreign key constraint enforcement

Question

I can't drop an index because some tables used it for foreign key

Msg 3723, Level 16, State 6, Line 1 An explicit DROP INDEX is not allowed on index 'tbl1.ix_cox'. It is being used for FOREIGN KEY constraint enforcement.

I tried to disable the index first before dropping

ALTER INDEX ix_cox On tbl1
DISABLE
Go

But still don't able to drop it.

Do I really need to remove the foreign key on those tables that used that index? Because it is about 30 tables.

Answer 1

You'll get the same exception if the index was created defining a PRIMARY KEY or UNIQUE constraint (https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-index-transact-sql?view=sql-server-2017).

In that case the simple solution is to use the ALTER-TABLE-command instead:

ALTER TABLE tbl1 DROP CONSTRAINT ix_cox

Answer 2

I am afraid this is the only option you have. you have to drop all foreign key constraints referencing to table and also you cannot recreate the foreign key constraints until you specify another unique index on the table from where you removed index.

Answer 3

Good day,

Here is a full example:

Let's create the tables and insert some sample data first

use tempdb
GO

-- NOTE! 
--    This sample code presents a POOR CODING where the user
--    does not explicitly named the objects
--    or explicitly create CLUSTERED INDEX

DROP TABLE IF EXISTS dbo.Users_tbl;
DROP TABLE IF EXISTS dbo.Categories_tbl;
GO

CREATE TABLE dbo.Categories_tbl(
    CategoryID INT IDENTITY(1,1) PRIMARY KEY
    , CategoryName NVARCHAR(100)
)
GO 
-- find the CLUSTERED INDEX created automatically for us
SELECT * FROM sys.indexes
    WHERE object_id = OBJECT_ID('Categories_tbl')
GO
-- you can notice that by default the PRIMARY KEY become CLUSTERED INDEX
-- If we did not configure a different CLUSTERED INDEX
-- In my case the automatic name was: PK__Categori__19093A2BAE0EA4C3

-- Let's create the secondary table
CREATE TABLE dbo.Users_tbl(
    UserID INT IDENTITY(1,1) PRIMARY KEY
    , UserName NVARCHAR(100)
    , CategoryID INT
    , FOREIGN KEY (CategoryID) REFERENCES Categories_tbl(CategoryID)
)
GO 

-- Insert sample data
INSERT Categories_tbl (CategoryName) VALUES ('a'),('b')
GO
INSERT Users_tbl(UserName,CategoryID)
    VALUES ('a',1),('b',1)
GO

SELECT * FROM Categories_tbl
SELECT * FROM Users_tbl
GO

REMOVE CLUSTERED INDEX from PRIMARY KEY

If we will try to DROP INDEX of the PK then we will get this error:

An explicit DROP INDEX is not allowed on index... It is being used for PRIMARY KEY constraint enforcement.

The solution is to drop the FK, Drop the PK, Create new PK with NONCLUSTERED index instead of CLUSTERED, and create the FK

/************************************************  */
/********* REMOVE CLUSTERED INDEX from PRIMARY KEY */
/************************************************  */
------------------------------------------------------ 
-- Step 1: DROP the CONSTRAINTs
------------------------------------------------------ 
---- Get FOREIGN KEY name
SELECT DISTINCT OBJECT_NAME(f.constraint_object_id)
FROM sys.foreign_key_columns f
LEFT JOIN sys.indexes p ON p.object_id = f.referenced_object_id
    WHERE p.object_id = OBJECT_ID('Categories_tbl')
GO

-- DROP FOREIGN KEY
ALTER TABLE dbo.Users_tbl   
    DROP CONSTRAINT FK__Users_tbl__Categ__59063A47 -- Use the name we found above
GO

---- Get PRIMARY KEY name
SELECT name FROM sys.indexes
    WHERE object_id = OBJECT_ID('Categories_tbl')
GO
-- DROP PRIMARY KEY
ALTER TABLE dbo.Categories_tbl
    DROP CONSTRAINT PK__Categori__19093A2B9F118674 -- Use the name we found above
GO


------------------------------------------------------ 
-- Step 2: CREATE new CONSTRAINTs
------------------------------------------------------ 
-- And now we can create new PRIMARY KEY NONCLUSTERED
--   Since we use PRIMARY KEY We need to have index,
--   but we do not have to use CLUSTERED INDEX
--   we can have NONCLUSTERED INDEX
ALTER TABLE dbo.Categories_tbl
    ADD CONSTRAINT PK_CategoryID PRIMARY KEY NONCLUSTERED (CategoryID);
GO
-- Finaly we can create the 
ALTER TABLE dbo.Users_tbl
    ADD CONSTRAINT FK_Categories_tbl 
    FOREIGN KEY (CategoryID)     
    REFERENCES dbo.Categories_tbl(CategoryID)
GO