Maintaing Soft Delete

Hi, if you are a developer dude you must know the very first concept of Foreign Key Constraint. While writing software applications which access the database, you do not need to worry about the foreign key constraint. DBMS maintains it for you and if you try to delete any row which belongs to a table (which doesn’t have cascade delete check enabled), you get a foreign key violation Exception and beengo!! You can catch the exception and show it to the user. Also it will be just the level of implementation you want, you can also catch the exception’s error code and can show a friendly message on the basis of that error code.

But what if your client belongs to some genius category and told you to make an application in which no record should be removed from the system. But application must also have the delete functionality so that user can delete the record from application but in database the record must be marked as “Deleted”. What you just have to do is to have an “Is_Deleted” bit column in each of your table. You will simply update its value to “True” on user delete action. At the same time all of your select queries must also include the check to get only those rows which have Is_Deleted equals to “False”.
But here comes a problem.
What if you a have a record in master table and that master table record also associate to some child table records. Marking that master table record as deleted will make your data inconsistent because now you will have some child records in your database whose master records are no more application level exists. Or in other words you have to maintain foreign key constraint on yourself.
A good but complete approach is to first check all child tables before marking any master table record as deleted. And if any of the child table records exists based on that master record then throw a custom exception from the delete sp/query.
Here is a simple sample procedure to accomplish the desired task

PS: My table’s IS Deleted Column’s name is “ISACTIVE_FLAG”


CREATE PROCEDURE [dbo].[sp_CORE_NA_SEAT_Delete]
(
@Original_NA_SEAT_ID int,

/***********Params required for Auditing*********/
@LAST_UPDATE_DATE datetime,
@LAST_UPDATED_BY bigint,
@IP nvarchar(1024),
@AUDIT_1 nvarchar(1024),
@AUDIT_2 nvarchar(1024),
@LOCATION nvarchar(1024)
/************************************************/

)
AS
SET NOCOUNT OFF;

IF EXISTS(
SELECT * FROM CORE_NA_SEAT INNER JOIN (SELECT * FROM CORE_ELECTORAL_AREA WHERE ISACTIVE_FLAG = 1) as E
on CORE_NA_SEAT.NA_SEAT_ID = E.NA_SEAT_ID
AND CORE_NA_SEAT.NA_SEAT_ID = @Original_NA_SEAT_ID)
OR
EXISTS(
SELECT * FROM CORE_NA_SEAT INNER JOIN VM_VOTER
on CORE_NA_SEAT.NA_SEAT_ID = VM_VOTER.NA_SEAT_ID
AND CORE_NA_SEAT.NA_SEAT_ID = @Original_NA_SEAT_ID)
OR
EXISTS(
SELECT * FROM CORE_NA_SEAT INNER JOIN VM_VOTER_REMOVAL_RECORD
on CORE_NA_SEAT.NA_SEAT_ID = VM_VOTER_REMOVAL_RECORD.NA_SEAT_ID
AND CORE_NA_SEAT.NA_SEAT_ID = @Original_NA_SEAT_ID)

BEGIN
RAISERROR ('National Assembly Seat can''t be deleted because it is being used by some other records.', 16, 1);
END
ELSE

UPDATE [CORE_NA_SEAT] SET [ISACTIVE_FLAG] = 0,
LAST_UPDATE_DATE = @LAST_UPDATE_DATE,
LAST_UPDATED_BY = @LAST_UPDATED_BY,
IP = @IP, AUDIT_1 = @AUDIT_1, AUDIT_2=@AUDIT_2,
LOCATION=@LOCATION

WHERE ([NA_SEAT_ID] = @Original_NA_SEAT_ID)