Disable transaction limits

Question

I am compiling a table that will be used to send subsequent messages to specific queries for information. The request is sent to a group of people, and responses are tracked. If the person does not respond, zero or more subsequent actions may 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 the message should be the first subsequent message, Follows will be null. Otherwise, this is the identifier of some other continuation. I also added a unique restriction on Follows. That is, any message can contain no more than one message.

EDIT: I must also allocate a foreign key for subsequent actions. It refers to the FollowupId in this table. Therefore, if A-> B-> C, just deleting B makes the foreign key in C invalid. Likewise, it is not possible to simply update C to follow A, because B is already following A, and a unique restriction prohibits duplication.

The problem, of course, is that deleting subsequent entries is difficult if this message is followed by something else. It seems to me that it should be possible to disable the constraint check so that you can remove the average check, "move" after the next steps, and then set the check again. Is there a way to disable the restriction on transaction time only?

(In addition, I am aware of the possible data inconsistency that occurs when RequestId is in this table. It might be better to have Followups [FollowupId, Message], InitialFollowups [FollowupId, RequestId] and AfterFollowups [FollowupId, Follows] I think this is unnecessary complicates this example.)

Answer 1

Disabling / enabling restrictions for some modifications is usually a bad idea, and performance can suck. Whenever you do this, make sure that your restriction is not only enabled, but also checked after completion.

In your case, you need to delete one line and change another. You should use MERGE if you are already on SQL 2008, which allows you to delete and update one command at a time.

Answer 2

I found that (at least on SQL Server) it is not possible to disable unique restrictions. You can disable the foreign key restriction, set the identifier of the deleted record to an invalid and impossible identifier (for example, -1 in my case), change the identifiers of subsequent actions, delete the corresponding record, and then resume the restriction check. Assuming the following data:

FollowId | Request | Next | Message
1 | 17 | NULL | "First"
2 | 17 | 1 | "Second, delete it."
3 | 17 | 3 | "Third, but make it second"

I used the following strategy:

// start the 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; 

// make a transaction

Answer 3

Update your other values ​​first, and then do the deletion.

So, if the order

A → B → C

and you delete B, update C. Keep track of A, A FollowedUp to C, and then delete B.