Disable Foreign Key Constraints with INSERT and UPDATE Statements

Applies to: yesSQL Server 2016 (13.x) and later YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

You can disable a foreign key constraint during INSERT and UPDATE transactions in SQL Server by using SQL Server Management Studio or Transact-SQL. Use this option if you know that new data will not violate the existing constraint or if the constraint applies only to the data already in the database.

Limitations and restrictions

After you disable these constraints, future inserts or updates to the column will not be validated against the constraint conditions.

Permissions

Requires ALTER permission on the table.

Use SQL Server Management Studio

To disable a foreign key constraint for INSERT and UPDATE statements

  1. In Object Explorer, expand the table with the constraint and then expand the Keys folder.

  2. Right-click the constraint and select Modify.

  3. In the grid under Table Designer, select Enforce Foreign Key Constraint and select No from the drop-down menu.

  4. Select Close.

  5. To re-enable the constraint when desired, reverse the above steps. Select Enforce Foreign Key Constraint and select Yes from the drop-down menu.

Use Transact-SQL

To disable a foreign key constraint for INSERT and UPDATE statements

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2012;  
    GO  
    ALTER TABLE Purchasing.PurchaseOrderHeader  
    NOCHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
    GO  
    
  4. To re-enable the constraint when desired, copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2012;  
    GO  
    ALTER TABLE Purchasing.PurchaseOrderHeader  
    CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
    GO  
    

Next steps