Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Disable Foreign Key Constraints with INSERT and UPDATE Statements

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

In This Topic

Limitations and Restrictions

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

Security

Permissions

Requires ALTER permission on the table.

Arrow icon used with Back to Top link [Top]

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, click Enforce Foreign Key Constraint and select No from the drop-down menu.

  4. Click Close.

Arrow icon used with Back to Top link [Top]

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, click New Query.

  3. Copy and paste the following examples into the query window and click Execute.

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

For more information, see ALTER TABLE (Transact-SQL).

Community Additions

ADD
Show:
© 2015 Microsoft