How to change the primary key to be non-clustered?

Question

Part-time reluctant DBA here. I want to change an existing primary key index from clustered to non-clustered. And the syntax is escaping me.

This is how it's scripted out right now.

ALTER TABLE [dbo].[Config] WITH NOCHECK ADD 
    CONSTRAINT [PK_Config] PRIMARY KEY  CLUSTERED 
    (
        [ConfigID]
    )  ON [PRIMARY]

I am not seeing an ALTER CONSTRAINT statement in the online docs.

score 78 · Accepted Answer · answered Apr 09 '10 at 01:01

78

Drop the clustered index, then recreate the primary key as non-clustered:

ALTER TABLE dbo.Config DROP CONSTRAINT PK_Config
go
ALTER TABLE dbo.Config ADD CONSTRAINT PK_Config 
    PRIMARY KEY NONCLUSTERED (ConfigID)

answered Apr 09 '10 at 01:01

Andomar

2

I understand this is an old post, however it is interesting to point out that, if the index is being used together with a primary key and it has been already referenced by another table, you won't be able to DROP it. (At least in MS SQL Server) – Bruno Nov 13 '19 at 10:12
@Bruno Is there an alternative to change the primary key to non-clustered if the index is being used together with a primary key and it has been already referenced by another table in MSSQL. – Kavya Shetty Jan 08 '20 at 00:39
@KavyaShetty Yes, use the answer from https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server to identify the referencing fkeys. And this to disable them (but choose a selective approach rather than the 'all' approach in the answer) https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql Re-enable when you have put the pkey back on... One commenter there usefully notees to check for triggers too! – simon coleman May 04 '21 at 16:53

1 Answers1