Removing Foreign Key Constraints against Hot Tables

The Issue?

The command asks for a Sch-M (Schema Modification Lock). This shouldn’t be a problem though since Table_A isn’t often used. The real issue lies with the fact that the Parent table (Table_B) is also affected by this command since every single insert (of which there are thousands happening ever second) is asking Table_A if the constraint we’re trying to remove is being violated.

This causes a circular wait chain that will eventually consume many resources and has the potential to take down your database!

At this point, the realization that I won’t be able to remove this set it and it was time to investigate “alternative solutions” 😃

The Solution!

I still had the requirement of dropping this constraint, but this change wasn’t important enough to schedule downtime.

Enter sp_rename! The idea here was to create a duplicate of Table_A named Table_A_NEW, absent the FK constraint. We would then find a way to “swap” this new table for the original, the hope being that Table_B will then continue to create rows, without being stopped by the FK constraint. Since sp_rename only involves locks that affect the two tables directly, it does not impact Table_B at all, since the sp_rename is does not involve the FK constraint

Now that the tables have been swapped out, all that was left was to clean up the original Table_A and truncate the records. Truncation removes any lingering considerations from operations on Table_B as there are no rows for the constraint to consider.

Caveats

This method is by no means a panacea, and with only small variations in circumstances it could end up increasing your pain. For instance if Table_A was highly active itself, being read from constantly, or worse written to, and this likely would not work out.

It’s also worth pointing out some alternatives:

• As mentioned above, you could schedule downtime. If the downtime will not cause hardships, or you already have repeating downtime scheduled this would eliminate any of the issues we faced. Removing the FK constraint becomes dead simple.
• You could add a few dramatic options to the DDL command like the following:

ALTER TABLE Table_A DROP CONSTRAINT FK_Table_B
WITH(
	WAIT_AT_LOW_PRIORITY
	(MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)

The ABORT_AFTER_WAIT = BLOCKERS option says that after waiting the maximum duration of 1 minute, it will force itself to the front of the queue and abort all sessions that are blocking this command.

In this situation, you will be forcefully terminating a potentially large number of operations all of which would need to be retried (if available in your application code)

---

About Matthew Preciado

With over 15 years of experience in software engineering, I have worked on various web development, cloud services, and e-commerce projects for leading companies in the travel, home services, and automotive industries.

As a Staff Software Engineer at Empower, I work on cross-cutting concerns on the Platform Team, enabling other developers throughout the organization.

I am proficient in C#, .NET, and other web technologies, and I enjoy working with agile teams and applying best practices to deliver high-quality and user-friendly solutions. I am passionate about creating innovative and impactful products that enhance the lives of people and solve real-world problems.

Connect with Matthew