ON DELETE NO ACTION constraint in SQL Server database
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 –
depName, and a child table with three columns –
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).
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.
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 ?
One Solution collect form web for “ON DELETE NO ACTION constraint in SQL Server database”
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.