en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


Read first, before you you use the blog

Maximize
יול23

Written by: ronen ariely
23/07/2018 08:38 RssIcon

In this sample code I will present a simple solution to the question "how to drop CLUSTERED INDEX from a PRIMARY KEY column. The case study is that the user created a table with PRIMARY KEY but he did not meant the PRIMARY KEY to become a CLUSTERED INDEX, which is what happen if you do not configure a different CLUSTERED INDEX or you explicitly configure the PRIMARY KEY as a NONCLUSTERED INDEX

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

   

In conclusion

Creating PRIMARY KEY might lead to creation of CLUSTERED INDEX, but we can have a different CLUSTERED INDEX if needed.

   

Categories: SQL
Location: Blogs Parent Separator Public blog