Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL database in Microsoft Fabric
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.
After you disable these constraints, future inserts or updates to the column will not be validated against the constraint conditions.
Requires ALTER permission on the table.
In Object Explorer, expand the table with the constraint and then expand the Keys folder.
Right-click the constraint and select Modify.
In the grid under Table Designer, select Enforce Foreign Key Constraint and select No from the drop-down menu.
Select Close.
To re-enable the constraint when desired, reverse the above steps. Select Enforce Foreign Key Constraint and select Yes from the drop-down menu.
To trust the constraint by checking the existing data in the foreign key's relationship, select Check Existing Data on Creation Or Re-Enabling and select Yes from the drop-down menu. This would ensure the foreign key constraint is trusted.
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute.
USE AdventureWorks2022;
GO
ALTER TABLE Purchasing.PurchaseOrderHeader
NOCHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;
GO
To re-enable the constraint when desired, copy and paste the following example into the query window and select Execute.
USE AdventureWorks2022;
GO
ALTER TABLE Purchasing.PurchaseOrderHeader
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;
GO
Verify that the constraint in your environment is both trusted and enabled. If is_not_trusted
= 1, then the foreign key does not check existing data when it is re-enabled or re-created. The query optimizer is therefore unable to consider potential performance improvements. Trusted foreign keys are recommended because they can be used to simplify execution plans with assumptions based on the foreign key constraint. Copy and paste the following example into the query window and select Execute.
SELECT o.name, fk.name, fk.is_not_trusted, fk.is_disabled
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS o ON fk.parent_object_id = o.object_id
WHERE fk.name = 'FK_PurchaseOrderHeader_Employee_EmployeeID';
GO
You should set the foreign key constraint to trusted if existing data in the table complies with the foreign key constraint. To set the foreign key to trusted, use the following script to trust the foreign key constraint again, noting the additional WITH CHECK
syntax. Copy and paste the following example into the query window and select Execute.
ALTER TABLE [Purchasing].[PurchaseOrderHeader]
WITH CHECK
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Documentation
Disable check constraints with INSERT and UPDATE statements - SQL Server
Disable Check Constraints with INSERT and UPDATE Statements
SET IDENTITY_INSERT (Transact-SQL) - SQL Server
Transact-SQL reference for the SET IDENTITY_INSERT statement. When set to ON, this permits inserting explicit values into the identity column of a table.
ALTER TABLE (Transact-SQL) - SQL Server
ALTER TABLE modifies a table definition by altering, adding, or dropping columns and constraints. ALTER TABLE also reassigns and rebuilds partitions, or disables and enables constraints and triggers.