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

Question

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?

Answer 1

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.

Answer 2

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';

Answer 3

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.

Answer 4

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.