none
How to disable the foreign Key constraint and enable after delete insert and update the table?

    Question

  • Hi,

     

    I have to disable thr foriegn key constraint and enable after Delete,Update and Insert.

     

    I ran the below script to diable the constraint,but its not diabled.

    EXEC sp_msforeachtable 'ALTER TABLE dbo.abc_test NOCHECK CONSTRAINT all'

     

    Thanks,

    Ron.

    Monday, September 05, 2011 4:31 PM

Answers

  • I think the above statement will disable only the CHECK constraint not the forieghn keys. Check the below thread for more explanation from Hunchback.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f27896be-a07f-4c79-9a72-d71f172d0761

     

    In order to drop and recreate the constraints, you have to run the below code and save the results in a text file and execute them seperately.

    --
    -- DROP CONSTRAINTS 
    --
    SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
    ' DROP  CONSTRAINT ' + '[' + f.name  + ']'
    FROM .sys.foreign_keys AS f
    INNER JOIN .sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    
    
    -- 
    -- RECREATE CONSTRAINTS
    --
    SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
    ' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
    +'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id)+')' as Scripts
    FROM .sys.foreign_keys AS f
    INNER JOIN .sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    



    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by KJian_ Monday, September 19, 2011 7:10 AM
    Monday, September 05, 2011 4:59 PM

All replies

  • I'm not sure what you are trying to do.  1) Disable every foreign key and check constraints on every table, 2) disable all the foreign key constraints on tables that are children of dbo.abc_test, 3) disable all foreign key constraints in dbo.abc_test that point to some other table where dbo.abc_test is the child table and also disable all check constraints on dbo.abc_test, or 4) something else.  Note that the statement you gave is an inefficient way of doing 3).

    Tom

    Monday, September 05, 2011 4:49 PM
  • I think the above statement will disable only the CHECK constraint not the forieghn keys. Check the below thread for more explanation from Hunchback.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f27896be-a07f-4c79-9a72-d71f172d0761

     

    In order to drop and recreate the constraints, you have to run the below code and save the results in a text file and execute them seperately.

    --
    -- DROP CONSTRAINTS 
    --
    SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
    ' DROP  CONSTRAINT ' + '[' + f.name  + ']'
    FROM .sys.foreign_keys AS f
    INNER JOIN .sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    
    
    -- 
    -- RECREATE CONSTRAINTS
    --
    SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
    ' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
    +'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id)+')' as Scripts
    FROM .sys.foreign_keys AS f
    INNER JOIN .sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    



    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by KJian_ Monday, September 19, 2011 7:10 AM
    Monday, September 05, 2011 4:59 PM
  • Thanks for quick reply.

     

    I want to disable and enable the

     

    3) disable all foreign key constraints in dbo.abc_test that point to some other table where dbo.abc_test is the Parent/Child table and also disable all check constraints on dbo.abc_test

     

    Thanks,

    Ron.

    Monday, September 05, 2011 4:59 PM
  • In that case all you should need is

    ALTER TABLE dbo.abc_test NOCHECK CONSTRAINT all

    (What you have will do that, it just does it many, many times - once for every table in your database).  You only need to do it once. 

    What makes you think it is not working?  Like you get errors when you do it, or something else?  Can you post what you are doing (ideally a script that will let us reproduce the problem).

    Tom

    P.S., just a guess on why you think it's not working.  After you disable the constraints, are you doing a TRUNCATE TABLE on dbo.abc_test?  If so, disabling the constraints isn't enough to do a TRANCATE TABLE, you must actually ALTER TABLE ... DROP CONSTRAINT.

    Tom

    Monday, September 05, 2011 5:17 PM
  • I want to disable Foriegn key constraint on abc_test table.

     

    Thanks,

    Ron.

    Monday, September 05, 2011 5:25 PM
  • Hi Ron 

    Earlier you are saying that it is not working, How are you determining that it is not working.

    You can actually use the below select statement to see if there are any foreign key constraint enabled on your table. 

    select name,is_disabled
    from sys.foreign_keys
    where [type] = 'F' and referenced_object_id = object_id('abcd_test');
    where is_disabled = 1
    
    


    If you get any rows in the above table, please do re-run the below script

    EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
    


    Once this is done, you should not see any more rows for the select query posted above.

    Now you can do your bulk inserts/updates or deletes and once your work is done enable the constraints back using the below query

    EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
    
    

    Note: if you dont use the with CHECK after the ? the constraints looks like it is enabled, but sql server dont trust the constraint which will cause some performance issue. so please add the with check again while switching on the constraint.

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Monday, September 05, 2011 5:50 PM
  • I hope you realize how dangerous it is what you are trying to do.

    I would try to avoid disabling the constraints if possible (which should be always). Maybe in your situation, you could simply use an INSTEAD OF trigger to avoid the problems you are having.

    If you must disable the constraints, then make sure you lock out all other users for the relevant tables in order to prevent integrity problems. The easiest way to do that would be to run your code in single user mode.

     


    Gert-Jan
    Monday, September 05, 2011 7:21 PM
  • You can check this blog post

    Delete all data in database (when you have FKs)

    and adjust to only do for a single table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, September 05, 2011 8:41 PM
  • The following article deals with the same topic:

    http://sqlusa.com/bestpractices2005/disabletriggerconstraint/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Monday, September 12, 2011 7:50 AM
  • USE NO CHECK WHILE YOU ARE DOING YOUR TASK WHICH SHOULD NOT USE FORIEGN KEY
    Thanks & Regards, Leela Prasad
    • Edited by BKNLPRASAD Monday, September 12, 2011 8:31 AM
    Monday, September 12, 2011 8:27 AM