Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

Why does dropping foreign keys in sql server take long?

Asked by Ankit Yadav Apr 16, 2021 461 views 1 answer
Share

About this question

I have made a script that, one at a time, deletes all the foreign keys from a database, just like this:

ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1 ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1 ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2

What surprises me is that the script takes a long time: on average, 20 seconds for each DROP FK. Now, I understand that creating a FK may be a big deal, because the server has to go and check that the FK constraint is not infringed from the beginning, but dropping? What does a server do when dropping FKs that takes so long? This is both for my own curiosity, and to understand if there is a way to make things faster. Being able to remove FK (not just disable them) would allow me to be much faster during a migration, and therefore minimize downtime. How do I drop a foreign key in SQL Server?

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.