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 – depNo and depName, and a child table with three columns – emNo, emName and depNo (FK).

  • SQL Server stored procedure - inserting new rows
  • Should I index a bit field in SQL Server?
  • Suggestions for replication of data from MS Sql 2005 and MySql
  • FreeTDS: How to set charset of parameters running stored procedure
  • Create batch file to copy contents of one SQL Server database to another
  • sql server stuff float
  • 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 ?

  • What is the difference between primary, unique and foreign key constraints, and indexes?
  • How do I set default value for a foreign key column in sql server?
  • set null value in a foreign key column?
  • Validating UPDATE and INSERT statements against an entire table
  • Can FK constraints work on several tables?
  • What happens when I drop a clustered primary key in SQL 2005
  • 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.