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
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.
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.
"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."
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 ?
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.
Member
99 Points
275 Posts
Deleting grandparent record, then parent records, then children records
May 19, 2013 09:43 PM|DonnieS|LINK
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
All-Star
193986 Points
28020 Posts
Moderator
Re: Deleting grandparent record, then parent records, then children records
May 20, 2013 12:50 AM|Mikesdotnetting|LINK
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.
Contributor
4946 Points
873 Posts
Re: Deleting grandparent record, then parent records, then children records
May 20, 2013 01:21 AM|GmGregori|LINK
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."
Member
99 Points
275 Posts
Re: Deleting grandparent record, then parent records, then children records
May 20, 2013 12:01 PM|DonnieS|LINK
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 ?
All-Star
193986 Points
28020 Posts
Moderator
Re: Deleting grandparent record, then parent records, then children records
May 20, 2013 04:24 PM|Mikesdotnetting|LINK
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.
Member
99 Points
275 Posts
Re: Deleting grandparent record, then parent records, then children records
May 20, 2013 04:59 PM|DonnieS|LINK
Thanks very much - very helpful.