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.)

score 3 · Accepted Answer · answered May 17 '10 at 19:52

3

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.

answered May 17 '10 at 19:52

A-K

16,804
8
54
74

Often a bad idea, but sometimes, a really good idea: [How to truncate a foreign key constrained table?](https://stackoverflow.com/q/5452760/712526) – jpaugh May 16 '19 at 18:22
I second this comment. You may also mess with indexing leading to other issues. Also why not just soft delete to get around the constraints? – Jatin Morar Dec 29 '22 at 18:50

score 2 · Answer 2 · edited Dec 29 '22 at 17:37

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.)

Note that the downside to using this approach is when the `CHECK CHECK CONSTRAINT` step is executed the constraint will be verified for the _entire table_ which has major performance implications. A better approach would be to use deferrable constraints, however SQL Server still doesn't support them. — Dai, May 19 '21 at 00:19

score 0 · Answer 3 · answered May 17 '10 at 18:42

0

Update your other values first, and then do your delete.

So if the order is

A -> B -> C

and you're deleting B, update C's Follows to A, A's FollowedUp to C, and then delete B.

answered May 17 '10 at 18:42

Dave Markle

95,573
20
147
170

Disable constraints during transaction

3 Answers3