How do I remove an asp.net identity user when constraints have been placed on the table?

Question

In this ActionResult I am deleting users from my ASP.NET Users table.

public ActionResult Delete(string UserName)
{
    var thisUser =
    _context.Users.FirstOrDefault(u => u.UserName.Equals(UserName, StringComparison.CurrentCultureIgnoreCase));
    _context.Users.Remove(thisUser);
    _context.SaveChanges();
    return RedirectToAction("Index");
}

However when I try to delete a user I get the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.UserLogs_dbo.AspNetUsers_Customer_Id". The conflict occurred in database "MYDATABASE", table "dbo.UserLogs", column 'Customer_Id'. The statement has been terminated.

I know that the constraints are put there to prevent data corruption and this is fine, but how would I go about removing the constraints just enough for me to remove the user and then add them back after the user is removed?

Answer 1

In short, you are in trouble. But there are ways to fix it.

You don't want to mess with the foreign keys just to delete a user, yet constrains are there for a good reason. And you want to delete the user records.

Let's see the scenario - user registers, does something on your site that generate a few log records with user id referencing back to the user. Then user decides that he no longer likes your system and deletes his profile. If you remove FK constraint, then remove a record from Users table, then restore the constrain, you will get records in Logs table that contain UserId. But 2 months down the line you want to look up that user - you'll find that the ID does not point anywhere. That makes the logs pretty useless.

So your options are:

• Soft Delete - don't actually delete user records, but set a flag that they have been deleted and make sure that all the systems that access Users table know about this flag and ignore soft-deleted records.
• Denormalise user information into logs. Do not have a FK constraint into logs from Users, but keep in logs userId, username, possibly email. This way you'll still know what user done what, and you'll be able to trace actions of deleted users. And you won't need to mess about with FK when you trying to delete a user. This is what I'm doing in my systems, but I'm not logging into DB, but into other storage where FKs to SQL are impossible.