What is the best practice to work in unmanaged programming pattern

Question

I have a query

UPDATE dbo.M_Room
 SET

 //do something
WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId

Now suppose PK_RoomId is my Pk of M_Room and is autoincremented field. So according to this I could have used

WHERE PK_RoomId= @RoomId

rather than

WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId

What all threats I could overcome if I use the second condition rather than 1st one. If we don't have any relationship/Constraints(PK, FK etc.) physically exists and cant implement due to unmanaged structure of database.

What will be your recommendation in such scenario. What all things should be done to keep data consistent.

Answer 1

I don't think it's a good idea to change the WHERE to just WHERE PK_RoomId= @RoomId. The first part (the part you want to keep) is for identifying the record. The second part (AND IsActive=1) is used to maybe restrict the update based on whether the room is active or not. About the last part (AND FK_DepartmentId =@DepartmentId), that could mean that sometimes you only want to update the room if it belongs to the department you specified. This could also be useful.

Why exactly would you want to change the query?

Answer 2

If you are using READ-UNCOMMITTED transactions or no transactions at all, or the data has been sitting round on someone's screen for a long time, the additional conditions could save you from a buried update, presuming that your // do something does something to the IsActive column.

It could also be a final guard against just getting it wrong (seeing if the room isn't active and then forgetting to make use of the fact).

Make sure to check the number of rows updated in either case.

Your second-last paragraph suggests the room_id may not be unique when it is supposed to be; you will always have trouble if that's the case.

Myself, I'd be inclined to check explicitly for buried updates if I suspected they may occur, and I'd think that form of defending against programming errors to be unusual.