You can drop and recreate foreign key constraints in SQL Server using a script that queries the sys.foreign_keys, sys.foreign_key_columns, and sys.objects system catalogue views to generate the exact ALTER TABLE statements needed. This approach is far safer than manually scripting constraints, because it captures the precise constraint names, column mappings, and referenced tables automatically.

Why Would You Need to Drop and Recreate Foreign Key Constraints?

There are legitimate scenarios where temporarily removing foreign key constraints is the most practical path forward. Bulk data migrations are the most common. When you're loading large volumes of data into a table that has foreign key relationships, SQL Server validates every row against the referenced table. On a table with millions of rows, that validation overhead adds up fast and can turn a 10-minute load into a 2-hour ordeal.

Other common scenarios include:

  • Data repairs - Fixing orphaned records or correcting referential integrity violations that already exist in the database
  • Schema refactoring - Restructuring table relationships during a development or migration project
  • Large-scale UPDATE or DELETE operations - Where the constraint temporarily blocks the operation due to cascading dependencies
  • Restoring data to a subset of tables without restoring the full database

The critical rule is straightforward: if you drop a foreign key constraint, you must be able to recreate it exactly as it was. That means capturing the constraint definition before you drop anything.

The Risk You Cannot Ignore

Dropping foreign key constraints on a production system, even briefly, creates a window where invalid data can enter the database. If a process inserts a row with a foreign key value that doesn't exist in the parent table during that window, you'll have an integrity violation. Recreating the constraint will then fail, and you'll be left with a database in an inconsistent state.

On high-transaction systems, that window can be dangerous. Even a 30-second gap with constraints disabled can result in hundreds of invalid rows depending on your insert rate.

The safest approach is to do this work during a maintenance window, with application connections blocked or the database set to restricted user mode. Never drop foreign key constraints on a live production system without a tested rollback plan.

The Script: Generate Drop and Recreate Statements Automatically

The script below generates two sets of statements for a given table and schema. The first set drops all foreign key constraints on that table. The second set recreates them. Run the script, capture the output, and save the recreate statements somewhere safe before you execute any drops.

SET NOCOUNT ON

DECLARE @table  SYSNAME
DECLARE @schema SYSNAME

SELECT
    @table  = 'TABLE',
    @schema = 'SCHEMA'

PRINT '/* Drop Foreign Key Statements for [' + @schema + '].[' + @table + '] */'

SELECT
    'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] DROP CONSTRAINT [' + fk.name + ']'
FROM sys.foreign_keys fk
INNER JOIN sys.objects o
    ON fk.parent_object_id = o.object_id
WHERE
    o.name = @table
    AND SCHEMA_NAME(o.schema_id) = @schema

PRINT '/* Create Foreign Key Statements for [' + @schema + '].[' + @table + '] */'

SELECT
    'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + '] ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY ([' + c.name + '])
REFERENCES [' + SCHEMA_NAME(refob.schema_id) + '].[' + refob.name + ']([' + refcol.name + '])'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.foreign_keys fk
    ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.objects o
    ON fk.parent_object_id = o.object_id
INNER JOIN sys.columns c
    ON fkc.parent_column_id = c.column_id
    AND o.object_id = c.object_id
INNER JOIN sys.objects refob
    ON fkc.referenced_object_id = refob.object_id
INNER JOIN sys.columns refcol
    ON fkc.referenced_column_id = refcol.column_id
    AND fkc.referenced_object_id = refcol.object_id
WHERE
    o.name = @table
    AND SCHEMA_NAME(o.schema_id) = @schema

To use the script, replace 'TABLE' and 'SCHEMA' with your actual table name and schema name. For example, to target the Orders table in the Sales schema:

SELECT
    @table  = 'Orders',
    @schema = 'Sales'

How the Script Works

The script queries three key system catalogue views that SQL Server maintains for every database:

  • sys.foreign_keys - Contains one row per foreign key constraint, including the constraint name and the parent object ID
  • sys.foreign_key_columns - Maps each foreign key to its parent column and the referenced column in the parent table
  • sys.objects - Provides table names and schema IDs for both the child and parent tables
  • sys.columns - Supplies the actual column names for both sides of the relationship

By joining these views together, the script reconstructs the full ALTER TABLE ... ADD CONSTRAINT syntax needed to recreate each constraint. This is the same approach Microsoft recommends for programmatically scripting constraint definitions, and it works across SQL Server 2012 through SQL Server 2022.

Step-by-Step Process for Safely Dropping and Recreating Foreign Keys

Follow this sequence to minimise risk:

  1. Run the script against your target table and capture the full output from SSMS
  2. Save the recreate statements to a SQL file or paste them into a separate query window. Do not proceed without this
  3. Test the recreate statements on a non-production copy of the database to confirm they execute without errors
  4. Schedule a maintenance window and notify affected application teams
  5. Restrict connections to the database if possible, using ALTER DATABASE [YourDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
  6. Execute the drop statements
  7. Perform your data operation (bulk load, repair, migration, etc.)
  8. Execute the recreate statements immediately after
  9. Verify constraint recreation by querying sys.foreign_keys and confirming all constraints are present
  10. Restore normal access with ALTER DATABASE [YourDB] SET MULTI_USER

Step 3 is the one most people skip. Don't. If the recreate statements fail in production, you need to know that before you're in the middle of a maintenance window.

What the Script Doesn't Handle

This script covers the most common foreign key pattern: a single column in the child table referencing a single column in the parent table. There are a few scenarios where you'll need to extend it:

  • Multi-column foreign keys - Where a composite key spans two or more columns. The script as written will generate separate ADD CONSTRAINT statements per column rather than a single composite definition
  • ON DELETE and ON UPDATE actions - Cascade, set null, and set default behaviours are not captured. If your constraints use these options, you'll need to add them manually to the recreate statements
  • Disabled constraints - Constraints created with WITH NOCHECK or currently disabled via ALTER TABLE ... NOCHECK CONSTRAINT have a different state that this script doesn't preserve

For environments with complex constraint configurations, use SSMS to script the table definition as a backup before making any changes.

Key Takeaways

  • Dropping and recreating foreign key constraints in SQL Server is a legitimate maintenance technique, but it requires capturing the exact constraint definition before dropping anything. This script automates that capture.
  • Always save and test the recreate statements before executing any drops. A failed recreate on production is a serious incident.
  • Restrict database access during the operation on busy systems. Even a short window with constraints dropped can allow invalid data to enter.
  • The script queries sys.foreign_keys, sys.foreign_key_columns, sys.objects, and sys.columns to reconstruct the full constraint definition, and works across SQL Server 2012 through 2022.
  • Multi-column foreign keys and constraints with cascade actions require manual additions to the generated recreate statements.

If your team is regularly dealing with constraint management, bulk data operations, or schema changes in production environments, it's worth having a structured process in place rather than relying on ad-hoc scripts. DBA Services provides SQL Server health checks and managed DBA support for Australian organisations that need reliable, expert oversight of their database environments. Get in touch to find out how we can help.