how to disable foriegn key for one insert in sql server stored procedure?

reza yari 0 Reputation points
2023-05-25T07:58:32.8166667+00:00

i want to insert in to table in my stored procedure but this approach take to much to check foriegn key constrain

i want to disable it temporary in my stored procedure

any suggestion?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,345 questions
{count} votes

4 answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-05-25T08:07:47.8733333+00:00

    Hi @reza yari

    You can refer to this thread and try the TSQL statement inside.

    https://stackoverflow.com/questions/19727865/how-to-turn-off-on-all-the-foreign-keys-and-triggers-in-microsoft-sql-server

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Khan Abrar Ahmed 0 Reputation points
    2023-05-25T11:59:51.6233333+00:00
    1. Disable the foreign key constraints at the beginning of your stored procedure.
    2. Perform the necessary insert operations.
    3. Re-enable the foreign key constraints at the end of your stored procedure.

    To disable the key use below script:

    EXEC sp_msforeachtable 'ALTER TABLE <Table Name> NOCHECK CONSTRAINT ALL';

    Re-Enable the the key use below script:

    EXEC sp_msforeachtable 'ALTER TABLE <Table Name> WITH CHECK CHECK CONSTRAINT ALL';

    0 comments No comments

  3. Erland Sommarskog 106.8K Reputation points
    2023-05-25T21:34:50.5833333+00:00

    This is dubious. Yes, you can do this, but what if there are concurrent inserts while your operation is running? And what if the FK is not re-enabled?

    You can do:

    BEGIN TRANSACTION
    ALTER TABLE SomeTable NOCHECK CONSTRAINT fk
    INSERT SomeTable SELECT ... FROM
    ALTER TABLE SomeTable WITH CHECK CHECK CONSTRAINT fk
    COMMIT TRANSACTION
    

    By wrapping the operation in a transaction, you are guaranteed that the FK will always re-enabled and re-evaluated when your operation has Completed. And the same if the operation fails and is rolled back.

    But the table will be blocked for everyone else, since you will be holding a Sch-M lock.

    And re-evaluating the constraint will also take time.

    This is something you may consider for a very special one-off operation when you need to load lots of data. But in a stored procedure that runs regularly? I'm less keen on that.

    0 comments No comments

  4. Bruce (SqlWork.com) 61,491 Reputation points
    2023-05-25T21:59:26.8566667+00:00

    note: you will not be able to re-create the constraint, if any inserts/updates done while dropped, do not have a valid foreign key.

    0 comments No comments