none
how to delete a row from a table when the table has dependencies

    Question

  • Hi all.
    i am trying to delete a customer from my customers table.when i try "delete customers where customerID='anton'(as a test)" it gives me this error:


    The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers". The conflict occurred in database "NorthwindDevTest", table "dbo.Orders", column 'CustomerID'.

    i saw something about on delete cascade but i dont know how to alter my table to use this funcion.

    please help

    Thursday, April 09, 2009 8:54 AM

Answers

  • First delete the existing Foreign Key constraint define on child table.  And then create it with following script

    ALTER TABLE ChildTable   ADD  CONSTRAINT [FK_B_a] FOREIGN KEY([ID])
    REFERENCES ParentTable ([ID])
    ON DELETE CASCADE
    After that whenver you will delete any row from Parent table all the refering rows from child table will also be deleted.
    Mangal Pardeshi
    SQL With Mangal
    • Marked as answer by scrywas Thursday, April 09, 2009 1:12 PM
    Thursday, April 09, 2009 9:09 AM
  • Hi,

    Mangal is correct.

    But if you want to restrict delete when order is places for that customer, then you can not use CASCADE.

    In that case, you need this restriction, but if you still want to delete that customer then first you need to delete all orders of that patient and then delete the customer.

    Let me know if it helps you in any way.

    Thanks.




    Tejas Shah
    Tejas Blog
    • Marked as answer by scrywas Thursday, April 09, 2009 1:11 PM
    Thursday, April 09, 2009 9:12 AM

All replies

  • First delete the existing Foreign Key constraint define on child table.  And then create it with following script

    ALTER TABLE ChildTable   ADD  CONSTRAINT [FK_B_a] FOREIGN KEY([ID])
    REFERENCES ParentTable ([ID])
    ON DELETE CASCADE
    After that whenver you will delete any row from Parent table all the refering rows from child table will also be deleted.
    Mangal Pardeshi
    SQL With Mangal
    • Marked as answer by scrywas Thursday, April 09, 2009 1:12 PM
    Thursday, April 09, 2009 9:09 AM
  • Hi,

    Mangal is correct.

    But if you want to restrict delete when order is places for that customer, then you can not use CASCADE.

    In that case, you need this restriction, but if you still want to delete that customer then first you need to delete all orders of that patient and then delete the customer.

    Let me know if it helps you in any way.

    Thanks.




    Tejas Shah
    Tejas Blog
    • Marked as answer by scrywas Thursday, April 09, 2009 1:11 PM
    Thursday, April 09, 2009 9:12 AM
  • Thanks guys for helping me. and Tesa, i read hat u gave me and it is helpful alot. . .im still gonna read more indepth into it.
    • Marked as answer by scrywas Thursday, April 09, 2009 1:11 PM
    • Unmarked as answer by scrywas Thursday, April 09, 2009 1:11 PM
    Thursday, April 09, 2009 1:00 PM
  • referential integrity wont allows the parent table to delete unless the related rows delete from the foreign keys. The chain branches of parent tables are simple or complex as one node might have multiple branches , if you are in this situation then have a look at the procedure in the blog http://www.calsql.com/2011/10/handling-relational-data.html
    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)
    Thursday, November 03, 2011 4:33 PM