Sometimes you may need to disable your primary key in MS SQL Server. You can delete your primary key directly but I recommend if you want to delete primary key, you can disable it first then you can delete it any time after observing for some time.  You can disable a primary key using the ALTER INDEX statement in MS SQL Server.

On the other hand, if you want to improve loading performance of a large table in a data warehouse, it is recommended to disable constraints such as PRIMARY KEY, UNIQUE KEY and CHECK and INDEX.
Keeping in mind that when you mark one of your table columns as the primary key, it creates a Clustered Index for the column you picked. If the Clustered Index is already created (before creating the primary key), then it creates a Non-Clustered Index. If the primary key is created with a Clustered Index and if you disable it, table will not be able to accessed because the main structure is disabled. However, if the primary key is created with a non-clustered index, you can disable it and still work with it.

The syntax to disable a primary key using the ALTER INDEX statement in MS SQL Server is:

ALTER INDEX constraint_name 
ON table_name

Let’s look at an example of how to disable a primary using the ALTER INDEX statement in SQL Server:

ALTER INDEX idx_tblt_emp_id_pk
ON [DataAnalytics].[dbo].[tblt_emp]

In this example, we would disable the primary key on the employees table called employees_pk.

I have an another tutorial related to this tutorial where you will learn How To ENABLE a PRIMARY KEY in MS SQL Server.

There is also an another important tutorial MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server which I think you will be benefitted most if you are working in this area. You can bookmark this page as your helping reference library.

In this tutorial, I have shown how to disable primary key in SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like the facebook page and stay connected.

Add a Comment