locked
How do I add cascade on delete to an existing table? RRS feed

  • Question

  • I want the deletes for a table to cascade.  How do I add this to a table?  Should it be done as a constraint?  Can someone post an example of how to add it?  thanks.
    Saturday, June 26, 2010 4:00 PM

Answers

  • Hi there,

     Try this:

    ALTER TABLE ChildTable
    ADD CONSTRAINT FK_ChildTable_Id
    FOREIGN KEY (Id)
    REFERENCES ParentTable (Id) ON DELETE CASCADE

     

    Hope it helpful.


    Kiran
    • Proposed as answer by Naomi N Sunday, June 27, 2010 3:32 AM
    • Marked as answer by KJian_ Friday, July 2, 2010 7:00 AM
    Saturday, June 26, 2010 5:44 PM
    • Proposed as answer by Naomi N Sunday, June 27, 2010 3:32 AM
    • Marked as answer by KJian_ Friday, July 2, 2010 7:00 AM
    Saturday, June 26, 2010 6:01 PM
  • Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted.

    In CASCADE feature if a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted.

     If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.

    By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.

    The following example declares that a line item row cannot exist without an associated order header row. The ON DELETE CASCADE option tells the database engine that if the parent ORDER HEADER row’s ORDERNUMBER is deleted, then any LINE ITEM tied to the ORDER HEADER by the deleted ORDERNUMBER should be automatically deleted as well.

    ALTER TABLE DBO.LINEITEM

    ADD CONSTRAINT FK_LINEITEM_ORDERNUMBER FOREIGN KEY (ORDERNUMBER)

    REFERENCES DBO.ORDERHEADER(ORDERNUMBER)

    ON DELETE CASCADE

     

    For more details go to here - Cascading Referential Integrity Constraints

    Thanks,


    Kapil Khalas
    • Proposed as answer by Shamas Saeed Monday, June 28, 2010 7:19 AM
    • Marked as answer by KJian_ Friday, July 2, 2010 7:00 AM
    Monday, June 28, 2010 6:24 AM

All replies

  • Hi there,

     Try this:

    ALTER TABLE ChildTable
    ADD CONSTRAINT FK_ChildTable_Id
    FOREIGN KEY (Id)
    REFERENCES ParentTable (Id) ON DELETE CASCADE

     

    Hope it helpful.


    Kiran
    • Proposed as answer by Naomi N Sunday, June 27, 2010 3:32 AM
    • Marked as answer by KJian_ Friday, July 2, 2010 7:00 AM
    Saturday, June 26, 2010 5:44 PM
    • Proposed as answer by Naomi N Sunday, June 27, 2010 3:32 AM
    • Marked as answer by KJian_ Friday, July 2, 2010 7:00 AM
    Saturday, June 26, 2010 6:01 PM
  • Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted.

    In CASCADE feature if a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted.

     If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.

    By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.

    The following example declares that a line item row cannot exist without an associated order header row. The ON DELETE CASCADE option tells the database engine that if the parent ORDER HEADER row’s ORDERNUMBER is deleted, then any LINE ITEM tied to the ORDER HEADER by the deleted ORDERNUMBER should be automatically deleted as well.

    ALTER TABLE DBO.LINEITEM

    ADD CONSTRAINT FK_LINEITEM_ORDERNUMBER FOREIGN KEY (ORDERNUMBER)

    REFERENCES DBO.ORDERHEADER(ORDERNUMBER)

    ON DELETE CASCADE

     

    For more details go to here - Cascading Referential Integrity Constraints

    Thanks,


    Kapil Khalas
    • Proposed as answer by Shamas Saeed Monday, June 28, 2010 7:19 AM
    • Marked as answer by KJian_ Friday, July 2, 2010 7:00 AM
    Monday, June 28, 2010 6:24 AM