Alternative to second column for tracking a FOREIGN KEY pre-deletion
I've worked with a SQL Server DB where the relationship between two tables was maintained by triggers instead of actual foreign keys (without the need for the second column).

Basically, in the "child" table:


on INSERT, check to make sure the inserted value in the "FK" column exists in the "parent" table;
on UPDATE, check to make sure any updated value in the "FK" column exists in the parent table.


In the "parent" table (using an AFTER trigger or equivalent):


on UPDATE, if the "PK" value is changed, take whatever action is appropriate if there are rows in the "child" table using the PK (change the "FK" column to  match; prevent the update if there are "children" already using the current value; do nothing; etc.)
on DELETE, take whatever action is appropriate if there are rows in the "child" table using the PK (prevent the deletion; set the value to NULL; or, in your case, do absolutely nothing).


Since the DB is not maintaining the relationship, you need to make sure you handle each of the possibilities above properly. It'll need to be documented as a known "gotcha" of the DB design, something that new devs get cued in on quickly, so they don't make assumptions regarding it.

Also, remember that disabling triggers also disables this "foreign key" relationship (unlike a true FK).

I'm not necessarily saying this would be better than your history column solution (which has its own maintenance hassles), but it is an option.