how we delete a row from parent table when child table connected with paren table by forigen key.

Question

hi all i am getting a problem while i attenpting to delete a row from parent table, actuall i have created 2 table custmer and account.i make cutomer id(primary key) in customer and customer id (forigen key ) in account.after created it i have filled data inside both table. at this point while i am trying to delete a row from first table (customer ) then it give failure message is that it can't be deleted bcs it is refrenced as forigen key some thing like that............but while we delete row from account table then it's delete sucess fully. .......i want to function like that if i delete a row from parent table(customer) then its in child table that row which has same customer id (account table) is delete automatically............

Answer 1

You need to set up the foreign key with on delete cascade to achieve this.

For SQL Server 2008 see the article Cascading Referential Integrity Constraints

Edit Just to add a somewhat redundant health warning you should be aware that adding on delete cascade will mean that when you delete the row from the parent table associated rows from the child table will be deleted. However as this is exactly the behaviour you state that you want I can't see that would be an issue.

Answer 2

watch out on the cascade deletes! a user will accidentally click on the application's little trash can icon and delete the customer, and then all the cascades will remove every trace of that customer, orders, invoices, payments, history, etc from your database. After the user call you to tell you about their little mistake, you'll have to restore a backup and try to pull the info back into the database.

I would look into "soft deletes" where you only change the customer's status from "active" to "inactive". the rows is not deleted, preserving all foreign key data. This allows reports to run on the data, because it still exists, as well as easy an "undo".

Soft deletes are not the end all only way to go, it is a business decision on how to handle this, purge the data or mark it inactive. That is only something you can decide, because I don't know your application or business logic. I just thought that I would offer it as an alternative.

Answer 3

sorry bhai ? nishant