Disable constraints during transaction

Question

I am putting together a table that will be used to send follow up messages to certain requests for information. A request is sent to a group of people and responses are tracked. If a person fails to respond, zero or more follow ups might be sent. I created a table:

FollowupId int primary key, 
RequestId int foreign key (outside this example), 
Follows int foreign key (FollowupId), 
Message varchar

If a message is to be the first follow up message, Follows will be null. Otherwise, it is the id of some other Followup. I also added a unique constraint on Follows. That is, no more than one message can follow any given message.

EDIT: I should also highlight the foreign key on Follows. It references FollowupId in this table. So if A->B->C, just deleting B makes the foreign key in C invalid. Similarly, it's not possible to just update C to follow A because B is already following A and the unique constraint forbids the duplication.

The problem is, of course, that deleting follow up entries is now difficult if that message is followed by another. It seems to me that it should be possible to disable the constraint checking to make it possible to delete a middle followup, "move up" subsequent followups, and then reenable the checking. Is there some way to disable the constraint only for the duration of a transaction?

(Also, I am aware of the possible data inconsistency that arises by having RequestId in this table. It might be better to have Followups [FollowupId, Message], InitialFollowups [FollowupId, RequestId], and FollowingFollowups [FollowupId, Follows] tables. I think it unnecessarily complicates this example though.)

Answer 1

Disabling/enabling constraints for some modification is usually a bad idea, and performance might suck. Whenever you are doing it, make sure that your constraint is not only enabled but trusted after you are done.

In your case, you need to delete one row and modify another one. You should use MERGE if you already are on SQL 2008, that allows you to both delete and update in one command.

Answer 2

I've discovered that (at least on SQL Server) it's not possible to disable unique constraints. It is possible to disable a foreign key constraint, set the id of the record to be deleted to an invalid and impossible id (such as -1 in my case), alter the followup ids, delete the record in question, and then resume constraint checking. Assuming the following data:

FollowId | RequestId | Follows | Message  
--------------------------------------------------------------------
       1 |        17 |    NULL | "First one"  
       2 |        17 |       1 | "Second one, delete this one"  
       3 |        17 |       3 | "Third one, but make it the second"  

I used the following strategy:

BEGIN TRANSACTION;

ALTER TABLE RequestFollowups NOCHECK CONSTRAINT FK_Follows_FollowId;

UPDATE      RequestFollowups SET Follows = -1 WHERE FollowupId = 2;
UPDATE      RequestFollowUps SET Follows =  1 WHERE FollowupId = 3;
DELETE FROM RequestFollowups                  WHERE FollowupId = 2;

ALTER TABLE RequestFollowups WITH CHECK CHECK CONSTRAINT FK_Follows_FollowId;

COMMIT TRANSACTION;

(Note that the CHECK CHECK on the penultimate line is intentional and not a typo.)