Should I Use the Cascade Delete Rule

Should I use the CASCADE DELETE rule?

ON DELETE CASCADE is fine, but only when the dependent rows are really a logical extension of the row being deleted. For example, it's OK for DELETE ORDERS to delete the associated ORDER_LINES because clearly you want to delete this order, which consists of a header and some lines. On the other hand, DELETE CUSTOMER should not delete the associated ORDERS because ORDERS are important in their own right, they are not just attributes of a customer.

One way to think about this is: if I issue DELETE X and it also deletes Y, will I be happy or unhappy? And if I issue DELETE X and am told "cannot delete X because Y exists" will I be glad of the protection, or irritated at the inconvenience?

Is it bad to rely on foreign key cascading?

I'll preface this by saying that I rarely delete rows period. Generally most data you want to keep. You simply mark it as deleted so it won't be shown to users (ie to them it appears deleted). Of course it depends on the data and for some things (eg shopping cart contents) actually deleting the records when the user empties his or her cart is fine.

I can only assume that the issue here is you may unintentionally delete records you don't actually want to delete. Referential integrity should prevent this however. So I can't really see a reason against this other than the case for being explicit.

When/Why to use Cascading in SQL Server?

Summary of what I've seen so far:

• Some people don't like cascading at all.

Cascade Delete

• Cascade Delete may make sense when the semantics of the relationship can involve an exclusive "is part of" description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
• The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.
• You should not use Cascade Delete if you are preserving history or using a "soft/logical delete" where you only set a deleted bit column to 1/true.

Cascade Update

• Cascade Update may make sense when you use a real key rather than a surrogate key (identity/autoincrement column) across tables.
• The canonical example for Cascade Update is when you have a mutable foreign key, like a username that can be changed.
• You should not use Cascade Update with keys that are Identity/autoincrement columns.
• Cascade Update is best used in conjunction with a unique constraint.

When To Use Cascading

• You may want to get an extra strong confirmation back from the user before allowing an operation to cascade, but it depends on your application.
• Cascading can get you into trouble if you set up your foreign keys wrong. But you should be okay if you do that right.
• It's not wise to use cascading before you understand it thoroughly. However, it is a useful feature and therefore worth taking the time to understand.

Proper cascade delete

You should decide between not being able to delete or inserting null in places of foreign-key when you delete, you can let the User to be deleted and insert null for any deleted Event that was foreignkey in Review and have orphaned Review, here is the code for making Event to Review relationship nullable:

modelBuilder.Entity<Review>()
           .HasOptional(t => t.Event)
           .WithMany(t => t.Reviews)
           .HasForeignKey(d => d.EventId)
           .WillCascadeOnDelete(false);

           modelBuilder.Entity<Review>()
            .HasOptional(p => p.Event)
            .WithMany(p => p.Reviews)
            .HasForeignKey(p => p.EventId);

also remember to lazy load the Event and Review with Include when deleting the user otherwise null will not get inserted, other point to make is that remember when you face situations like this when User have many Events and Events have many Reviews you have to do the null insertion for Event to Review yourself, EF doesn't do this for you, something like blow example:

var eventItems = _db.Events.Include(p => p.Reviews).(s => s.UserIdfk == UserId);

                foreach (var item in eventItems)
                {
                    _db.Events.Remove(item);
                }

I hope I've been able to convey the general approach.

SQL Delete vs. Cascade

To use cascading deletes, you would need to have foreign key constraints between the tables. You would set up the cascading delete rule when defining the foreign key, like so:

ALTER TABLE contactdetails ADD
CONSTRAINT FK_contactdetails_company_id FOREIGN KEY (company_id) 
REFERENCES organizations (idOrg)
ON DELETE CASCADE

This would tell the db that when a row is deleted from the organizations table, if there are any rows in contactdetails that reference it, those should be deleted as well.

As for whether this is a good idea or not - that's a matter of opinion. Personally, I don't like to use them since it makes it too easy to accidentally delete lots of data that maybe you didn't intend to delete. But others will disagree I'm sure.

How to define conditional cascade delete rule in Core Data?

You can put your deletion logic in your -prepareForDeletion method on your NSManagedObject. You should be able to assert any policy you want then.

Using Cascade Delete Rule and validateForDelete on a One-to-Many Relationship in iPhone Core Data

First, your call to super is ignoring its response and it is not acting on the response from the super. That is generally a bad idea.

Second, when you say NO to the validateForDelete it will throw an exception because the delete rule cannot be completed; in this case the cascade delete. In short, the validate method is not the correct place to be trying to handle this situation.

To handle this situation you should be overriding the -prepareForDeletion method in the A class and have it peek at any Bs that fit that situation and delete them as appropriate. You will also want to change the delete rule to nullify instead of cascade. I would implement it as follows:

- (void) prepareForDeletion
{
  [super prepareForDeletion];
  if (![self myB]) return; //I don't have a B
  if ([[[self myB] myAs] count] > 1) return; //Has more relationships
  [[self managedObjectContext] deleteObject:[self myB]];
}

This will check to see if you have a B and if you do if that B has more than one relationship and if it doesn't, add it to the queue for deletion. Otherwise it will allow Core Data to just nullify the relationship.

---

Related Topics