Enable constraint

Question

I have a huge database with many constraints and I have updated some data disabling constraint and I just want to enable them back.

I have used below query

EXEC sp_msforeachtable 'ALTER TABLE MyTable NOCHECK CONSTRAINT all' 
exec sp_msforeachtable @command1='print ''MyTable''', @command2='ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT all'

But when I run the enable query it takes a long time due to huge volume of data. It take more than 1 hour.

I just want to enable it quickly in the proper way without having any issues.

Please guide me how to overcome this issue.

score 2 · Accepted Answer · edited Nov 13 '18 at 23:12

2

Doing it "proper way without having any issue" is going to take time. You could improve the speed by omitting the WITH CHECK option, but then you'll end up with untrusted constraints in your DB, so I would not recommend that option.

edited Nov 13 '18 at 23:12

Community

1
1

answered Apr 21 '11 at 16:47

Joe Stefanelli

132,803
19
237
235

thanks your quick reply. I need to keep it back to normal stage. I dont want to messup anything. I think it takes time to enable due to checking existng data while enabling. Do we have option to disable data validation and enable rightaway.? – user459295 Apr 21 '11 at 16:53
@user459295: As I said, the faster option is to omit the `WITH CHECK`, which will have consequences. – Joe Stefanelli Apr 21 '11 at 17:19
I j't want to know the infact of untrusted constraints. I abled to run quickly removing WITH CHECK option. It ignor the data validation. – user459295 Apr 21 '11 at 21:09
@user459295: Read the article in the link I included in my answer to understand the impact of untrusted constraints. – Joe Stefanelli Apr 21 '11 at 21:12

score 1 · Answer 2 · answered Apr 21 '11 at 19:16

Using sp_msforeachtable runs the statements sequential. Most likely spreading things out over a few threads will speed up the process a lot. With Service Broker this can be done in SQL Code.

(Below is code written from memory, I have no SQL Server available to test this on. So there might be some bugs in it. I will update whenever I have a Server available.)

First create an activation procedure

  CREATE PROC p_enable_constraints
  AS
     DECLARE @handle UNIQUEIDENTIFIER
           , @message sysname
           , @sql nvarchar(max)

     WHILE 1=1
       BEGIN
         BEGIN TRAN
         WAITFOR ( RECEIVE TOP(1) @handle = conversation_handle,
                              @message = message_body
                   FROM ConstraintQueueReceive), TIMEOUT 1000;
        IF @@rowcount = 0
          begin
            rollback
            break;
          end

        set @sql = N'ALTER TABLE ' + quotename(@message) + N' WITH CHECK CONSTRAINT ALL'
        exec (@sql)
        if @@error <> 0
          begin
            rollback tran
            break
          end 
        COMMIT TRANSACTION
      END
    RETURN(0)
   GO

Set up the Queues and Services to send and receive messages

  CREATE QUEUE ConstraintQueueSend
  CREATE SERVICE ConstraintServiceSend

  CREATE QUEUE ConstraintQueueReceive 
    WITH STATUS = ON, ACTIVATION (PROCEDURE_NAME = p_enable_constraints
                                 , MAX_QUEUE_READERS = 8
                                 , EXECUTE AS SELF);

  CREATE SERVICE [ConstraintServiceReceive] ON QUEUE ConstraintQueueReceive

Now create a procedure to submit a message for each table

  CREATE PROC p_submit_enable_constraints_message @object sysname
  AS
    DECLARE @handle UNIQUEIDENTIFIER;
    DECLARE @message sysname;

    BEGIN TRANSACTION;
    BEGIN DIALOG @handle FROM SERVICE [ConstraintServiceSend] 
                           TO SERVICE [ConstraintServiceReceive]
    WITH ENCRYPTION = OFF;
    SEND ON CONVERSATION @handle (@object);

    COMMIT TRANSACTION;

    END CONVERSATION @handle
    GO

Finally we can call the procedure to submit this to the queue.

   EXEC sp_msforeachtable 'EXEC p_submit_enable_constraints_message N''?'''

Ideally the result of the alter table is captured and sent back.

score 0 · Answer 3 · answered Apr 21 '11 at 18:21

0

One thing to check is if you have indexes on both sides of the constraint (a good idea during normal operations, btw). To make this explicit, let's say that you have two tables (tbl_a and tbl_b) that have a FK constraint between them (let's say it's col_c). You'd want an index where the leading column is col_c on both tbl_a and tbl_b so that it doesn't have to table scan the table that doesn't have such an index.

Also, checking each constraint individually (rather than doing a "check constraint all") will give you better granularity should you need to stop in the middle.

answered Apr 21 '11 at 18:21

Ben Thul

31,080
4
45
68

looks grate, but can U tell me a way to trace every constraint related to table. then I know what to disable or enable individually. – user459295 Apr 21 '11 at 21:16
@user459295: The constraints are in sys.foreign_keys. Of note, the name column is the name of the constraint and parent_object_id is the object_id of the table to which the FK belongs. From there, you should be able to generate the necessary DDL statements. An alternative is powershell or your favorite way to work with SMO. But that's a horse of a different color. – Ben Thul Apr 22 '11 at 17:09

Enable constraint

3 Answers3