locked
Deleting grandparent record, then parent records, then children records RRS feed

  • Question

  • User483055775 posted

    These records are logically related by keys,  and when deleting the grandparent, i need to delete the parents that belong to the grandparent, but before that I need to delete the children, then the parents.  Are there examples or cases I could look at that do this. 

     

    My records are all related by keys, in that the child records have the unique ID key of the parent who has the unique ID key of the grandparent.  I did NOT link them as related within the database section as I have an issue with that in the past in other platforms, except those that support cascading deletion, and I doubt webmatrix's sql does.     One thing I want to do in the near future is convert this webmatrix app to use MySql - if that would be helpful - I would take the time to do it now.  Thanks

    Sunday, May 19, 2013 9:43 PM

Answers

  • User-821857111 posted

    Sql Server Compact Edition supports cascading deletes. Otherwise you have ot "walk" the cascade yourself, starting with the children and working your way up the relationship tree.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 20, 2013 12:50 AM

All replies

  • User-821857111 posted

    Sql Server Compact Edition supports cascading deletes. Otherwise you have ot "walk" the cascade yourself, starting with the children and working your way up the relationship tree.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 20, 2013 12:50 AM
  • User1713851783 posted

    I did NOT link them as related within the database section as I have an issue with that in the past in other platforms, except those that support cascading deletion, and I doubt webmatrix's sql does.

    From the SQL Server Compact Books Online :

    "For ON DELETE or ON UPDATE, if the CASCADE option is specified, the row is updated in the referencing table if the corresponding referenced row is updated in the parent table. If NO ACTION is specified, SQL Server Compact returns an error, and the update action on the referenced row in the parent table is rolled back.

    For example, you might have two tables, A and B, in a database. Table A has a referential relationship with table B: the A.ItemID foreign key references the B.ItemID primary key.

    If an UPDATE statement is executed on a row in table B and an ON UPDATE CASCADE action is specified for A.ItemID, SQL Server Compact checks for one or more dependent rows in table A. If any exist, the dependent rows in table A are updated, as is the row referenced in table B.

    Alternatively, if NO ACTION is specified, SQL Server Compact returns an error and rolls back the update action on the referenced row in table B when there is at least one row in table A that references it."

    Monday, May 20, 2013 1:21 AM
  • User483055775 posted

    Would it make sense to convert this to mysql at this point, this app has 10 tables, quite simple - but I am about to deploy and I optimistically expect to see a large number of attention very quickly ?  

    Monday, May 20, 2013 12:01 PM
  • User-821857111 posted

    If you expect large traffic, use a server-based system like SQL Server or MySQL. File-based systems like Sql Compact will scale quite well for sites that are largely read-only, but begin to struggle when there is a lot of writing.

    Monday, May 20, 2013 4:24 PM
  • User483055775 posted

    Thanks very much - very helpful.

    Monday, May 20, 2013 4:59 PM