ON DELETE NO ACTION constraint in SQL Server database

Question

What I want to do is when I delete a row from the parent table to keep the data in the child table.

Meaning that if I have a table department, which is the parent table with two columns - depNo and depName, and a child table with three columns - emNo, emName and depNo (FK).

I want to be able to delete the row in the parent table, but to keep the one in the child table (in my case it is connected with licenses and is a good idea to keep track of it, even if the user is already deleted).

I guess ON DELETE NO ACTION won't work, because it will give me an error if I try to delete the row in the parent table.

CASCADE and SET NULL are not good in this case.

SET Default - it won't work because I can't make a default value - it could be different (and its 100% sure it will be).

Any ideas how to make it ?

Answer 1

The only way to keep child data with the foreign key column still containing the original value is to remove the foreign key constraint, risking the ability to add inconsistent data.

I suggest creating a new table without the foreign key constraint and copy the child rows to it before deletion.