Muokkaa

Jaa

Disable foreign key constraints with INSERT and UPDATE statements

  • Artikkeli

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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.

  6. 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.

  • If Check Existing Data on Creation Or Re-Enabling is set to No, the foreign key does not check existing data when it is re-enabled. 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. To check whether foreign keys are trusted in your database, see a sample query later in this article.

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 AdventureWorks2022;  
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 AdventureWorks2022;  
GO  
ALTER TABLE Purchasing.PurchaseOrderHeader  
CHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
GO

  5. 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

Next steps