How do we delete a row from a parent table when a child table is linked to a paired table using the forigen key

Question

Hi everyone, I have a problem when I want to delete a row from the parent table, I created 2 custmer and account.i make cutomer id tables (primary key) in the client and client identifier (forigen key) in the account. After creating it, I filled in the data inside both tables. at the moment, while I am trying to delete a row from the first table (client), then it gives an error message, so that it cannot be deleted by bcs, it is redefined as forigen key, something like this .... ........ but while we delete the row from the accounts table, it completely removes sucess. ....... I want to work this way, if I delete a row from the parent table (client), then in the child table this row, which has the same client identifier (account table), is automatically deleted ..... .......

Answer 1

Watch for the removal of the cascade! the user accidentally clicks on the application icon the trash can icon and deletes the client, and then all cascades delete all traces of this client, orders, invoices, payments, history, etc. from your database. After the user calls you to tell you about their small error, you will have to restore the backup and try to return the information back to the database.

I would look at "soft deletes" where you change the client status only from "active" to "inactive". rows are not deleted, retaining all foreign key data. This allows reports to run data because it still exists, and is also easy to undo.

Soft deletion is not all that is needed, it is a business decision on how to handle it, clear data or mark it inactive. This is only what you can solve, because I do not know your application or business logic. I just thought I was offering this as an alternative.

Answer 2

To do this, you need to configure the foreign key on delete cascade .

For SQL Server 2008, see Cascading Referential Integrity Constraints

Change To add a somewhat redundant health warning, you should be aware that adding on delete cascade will mean that when you delete a row from the parent table, the related rows from the child table will be deleted. However, since this is exactly the behavior that you state that you want, I do not see that this will be a problem.