Cascades not implemented at the schema-level?

mindplay · Post by **mindplay** » Thu 19 Jan 2012 16:06

I just discovered that ED does not actually implement cascading updates and deletes at the database-level.

I realize that those aren't technically necessary for NH to work correctly - I can't speak for other ORMs, but NH will actually generate cascades of queries that make updates and deletes to related tables, so from an application standpoint, it's not technically a problem.

From a database administrator's point of view, however, not implementing cascades at the database-level, means that the schema is incomplete - the cascade-rules implemented by an application using NH will be inconsistent with those implemented at the database-schema level.

Let's take a simple example, Customer has many Orders, with cascading delete of orphaned Orders when a Customer is deleted.

What happens if someone has to go to the Customer table and manually delete a Customer for some reason? You end up with a bunch of orphaned Order records, and even if you delete those, potentially you end up with even more orphaned OrderLine records, and who knows what else.

Most modern RDBMS provide cascading updates and deletes at the schema-level for a good reason: data integrity.

If we can't leverage the capability to guarantee referential integrity, we're missing out on the real benefits of using an RDBMS in the first place.

To take another closely related example, imagine if the NOT NULL constraint was never generated. It isn't technically required - NH will enforce nullability constraints just fine without it. But what happens if someone has to manually make updates to the database, and indirectly violates nullability constraints in the application, that weren't implemented at the schema-level?

The reasoning for this is exactly the same.

You have a defined model in ED - a single source, from which you generate three consistent tiers: code, schema and mappings.

To the extend that it's possible, all three tiers should implement the constraints and rules defined by the model - for example, nullability is expressed with an annotation in the code, a constraint in the schema, and an attribute in the mapping.

Wouldn't it be natural that the schema also express the cascade-rules defined for the relationships in model?

Shalex · Post by **Shalex** » Mon 23 Jan 2012 17:32

Thank you for your suggestion. We will implement cascading updates and deletes at the database-level and post here about this.

Shalex · Post by **Shalex** » Wed 25 Jan 2012 13:10

Synchronization with cascading updates and deletes basing on the foreign key constraints is supported. We will post here when the corresponding build of Entity Developer is available for download.

Shalex · Post by **Shalex** » Fri 27 Jan 2012 14:29

New build of Entity Developer 4.2.120 is available for download now!
It can be downloaded from http://www.devart.com/entitydeveloper/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23252 .