CODEWITHSUNDEEP
sqlt-sql
Levitikon
Levitikon

Reputation: 7857

Best practices for sql database integrity when deleting tables that have FK constraints

Lets say I have a tasks and a log table:

tbl_tasks

Id     Name               ProjectId
-----------------------------------
1      Walk the dog       1
2      Check the mail     1

tbl_log

Id     TaskId    CreatedDateTime 
--------------------------------
1      1         6/3/13 7:18am    
2      2         6/3/13 7:18am      
3      1         6/3/13 7:18am

The log table should be a permanent record of events. When a user deletes a task however, I run into a integrity issue that I have a couple solutions in mind.

1. Instead of using TaskIn in the log table, use the task name. I'm not a fan of this approach, but it's the easy solution.

tbl_log

Id     TaskName           Credits 
---------------------------------
1      Walk the dog       2      
2      Check the mail     1      
3      Walk the dog       2

2. Introduce another table that enables tasks have a 1:m relationship to a project, but really it will be 1:1 and enforced by having the taskId as the pk (unique constraint). And instead of deleting the Task, delete the relationship between task and project. I'm not a fan of all the litter this leaves behind for all the tasks are severed that have no FKs, which I'm guessing will be the majority of cases.

tbl_tasksInProject

TaskId     ProjectId    
-------------------
1          1               
2          1

What is the best practice for dealing with this?

Upvotes: 2

Views: 226

Answers (3)

GarethD
GarethD

Reputation: 69789

I think the approach you take really depends on your requirements, rather than best practises.

It appears you want to keep the corresponding records in the log table even after the task is deleted (which in itself is not great practise, what is the point in keeping data that refers to nothing?). If this is the case then you may be best doing a soft delete (the benefits and drawbacks of this approach are discussed in the top two answers to this question).

i.e. Have BIT column called "IsDeleted", in Tasks, then instead of deleting, you just update the field to 1.

An alternative to this column would be to make ProjectID nullable, and change the referencial action trigger on the foreign key as follows:

ALTER TABLE tbl_tasks
ADD CONSTRAINT FK_tbl_Tasks_ProjectID FOREIGN KEY (ProjectID)
REFERENCES tbl_Projects (ID) ON DELETE SET NULL;

This way, if a project is deleted then the ProjectID in any associated tasks becomes NULL, you then know any Task with a NULL ProjectID has been "Deleted". This means you do not lose referencial integrity to your Task table.

Alternatively, if you do not want the soft delete approach and don't need to keep the log events for deleted tasks you could ofcourse just cascade the delete from the tasks table to the log table:

ALTER TABLE tbl_log
ADD CONSTRAINT FK_tbl_log_TaskID FOREIGN KEY (TaskID)
REFERENCES tbl_tasks (ID) ON DELETE CASCADE;

In summary, whichever way you choose be consistent, either delete all your data (ON DELETE CASCADE), or none of it (Soft deletes), don't delete half and you won't have a problem of referencial integrity.

Upvotes: 0

Serge
Serge

Reputation: 6712

Create a third table with the "name" and a nullable FK to "tbl_tasks".
Then make tbl_log references that third table.

So whenever tbl_tasks' rows get deleted, the references to those rows just gets null (or set them to a default "undeletable" tbl_tasks row)

Upvotes: 0

Andrew
Andrew

Reputation: 2335

Of the two solutions I'd go for holding the task name to be honest. If you keep simply the ID then how will you know to what it refers?

As a third option however why not have an additional field in tblTasks called Deleted?, which can be set to true when a task is deleted instead of actually deleting the record - effectively doing a soft-delete and maintaining your relationships?

Upvotes: 2

Related Questions