What makes this database's cascading deletes cyclical?_问答_开发者

What makes this database's cascading deletes cyclical?

开发者 https://www.devze.com 2023-01-16 20:32 出处：网络

Vendor (PK) - Id Name Stand (PK) - Id VendorId - fk - cascaded delete if Vendor is deleted) Name Item (PK) - Id

Vendor

(PK) - Id

Name

Stand

(PK) - Id

VendorId - fk - cascaded delete if Vendor is deleted)

Name

Item

(PK) - Id

VendorId - (fk - cascaded delete if Vendor is deleted)

Name

Price

UnavailableItem

(PK) - ItemId - (fk - cascaded delete if Item is deleted)

(PK) - StandId - (fk - cascaded delete if Stand is deleted)

The database above represents an athletic stadium.

Multiple Vendors exist (Bob's Pizza, Tom's Tacos...)

Multiple Stands exist for each Vendor (Bob's Pizza Concourse A, Bob's Pizza Concourse B...)

Items are configured by Vendor as all stands for a particu开发者_StackOverflowlar Vendor offer the same items at the same prices

Stands may run out of a particular item, so the UnavailableItems table has a record for each item that has become unavailable at a given stand (using a composite primary key of ItemId and StandId)

Problem:

I can create everything just fine until I add the last Foreign Key listed (FK_UnavailableItem_StandId_Stand_Id) with a Delete Rule: Cascaded

SQL Compact 3.5 (using VS 2010 Server Explorer) reports the following error: The referential relationship will result in a cyclical reference that is not allowed.

I understand that if I have an record in the UnavailableItem table, and its Vendor gets deleted, it will try to be deleted twice:

Once because its referenced item was deleted.

Once because its referenced stand was deleted.

However, this does not appear to be cyclical to me. The cascading-deletes branch into two paths (Deleted Stands and Deleted Items) that both end in a the same record being deleted... but it ends there. There is no infinite loop of cascading-deletes after that. Am I missing something, or is this a limitation of the tools I am using?

Thank you for any help you can provide!

You can't cascade delete through two branches, it is a limitation of the tool. But cascade delete is generally a bad thing to do to your poor innocent database anyway. Delete from the bottom table and move up. That way if you have 100000000000 records at the bottom you can do them in batches for performance. Cascade delete can cause performance problems.

Assuming SSCE does not allow cascade delete along two paths, you could remove the cascade delete for UnavailableItem.StandId.

At least, it will still cascade when a Vendor, or an Item is deleted.

And if UnavailableItem still contains rows for deleted Stands, at least they could be filtered out when joining UnavailableItem with Stand, when building refilling orders...