Delete no action rule

In the database I have a FK nullable relation delete rule set to "No action" which on Oracle is the rule not to allow deletion of parent records in case any child exists (some database engines, differentiate also a slightly different "Restrict" rule).

However, Lightspeed generated model is behaving as the the delete rule is "Set null" - if parent record is deleted, the child FK is set to null value.

I spent some time through help and forum and could find only information about cascade deletes or set null deletes. Maybe I am not seeing something obvious, but I would really need to know:

Is delete rule "No action" supported in Lightspeed (I mean by some kind of attribute or something, not by typing custom validation code for checking child records ourselves)?
If yes, is there a good reason why the designer is not following FK's "No action" rule?

LightSpeed doesn't have a "fail delete if children exist" mode: its only behaviours are "delete children when parent deleted" (for dependent relationships) and "orphan children when parent deleted" (for non-dependent relationships).  So you would need to write custom code to check for this condition I'm afraid.

Hi Ivan,

Is there any reason why you're not supporting this? I mean, it is a long-existing database (and hence relational data model) feature... Are you not even considering to implement it?

Hi Marko - This is a valid scenario but not one we have supported previously as Ivan described. We will look to add in some support for this in the runtime shortly and push it out in a nightly. We would look to add this as an option to specify the removal behavior for an association (the default would be as current, Set Null), however we will put in an option for No Action, which will throw an exception if there are children in the associated collection. Support for the designer to infer this from a schema will need to be done separately, so we will log a ticket on the backlog for this.

We will let you know once this is available in a nightly for you to download :)

Hi Jeremy. Thanks for your understanding and a quick reasonable solution.

I know you are all busy with 3.0, but I hope you will also understand a point of view that the current situation is tricky in the sense that Lightspeed designer is actually by itself changing the behavior of the data model present in the database (if we agree that delete rule is a part of data model behavior). So hopefully you will not treat the task of "designer following schema delete rules" at a really low priority.

And yes please, keep us updated as always. Thanks for a good support.

Hi! Is there any news on this?
Like I said on another topic, I don't have a habit of installing new nightlies, unless I see something new and interesting on your forum or blog, so perhaps, it's already implemented...

This issue is a bit of a high priority for us, as we currently have to be very careful not to miss FKs which need custome code not to allow deletes (and of course, it's a some more code to handle),

Hi Mark, we are still working on this one and will let you know directly once we have it up in a nightly :) Sadly its not a trivial addition, so it is taking a bit of time on our end to get this implemented.

 

Jeremy

Hi Marko,

We have added this in now and its available in the nightlies. To use the new behavior you will need to add a [RemovalBehavior] attribute to the EntityHolder on the child side of the association. You can then specify the Action to be applied, either SetNull (current behavior) or NoAction (throw exception if children exist when a parent is trying to be removed).

If you could let us know how you get on with this it would be much appreciated :)

 

Jeremy

Hi, I am testing this, and found the following issue: my test method made a delete (although it was not supposed to according to this new attribute), but when I did a debug with the same situation and was inspecting entities, delete was not allowed. Sounds to me like lazy loading of associations could be the cause, but you will know better.

Of course, the other "bump" here is that immediately when I press Save in the designer all my RemovalBehavior attributes are lost.

Hi Marko,

Thanks for looking at this so quickly - I have commited an update for the lazy load scenario above, and it will be in tonights nightly build (20090812) and above, if you could verify the behavior corrects what you are currently seeing.

In relation to the designer, yes, currently the designer does not support this attribute so it will indeed strip it from the entity when making an update. For now you will need to move the entity definition into its own class file to avoid this.

 

Jeremy

We have now added designer support and this will also be included in the 20090812 nightly -- click on the one-to-many association arrow and you should see a setting called Removal Behavior (in the Persistence section if you use categorised view).

I've tested it. Looks like it's working OK now. Thanks guys (BTW, I am really starting to suspect there is more than one guy behind Ivan's profile. How is it otherwise possible to cover all these topics :) )

The finishing touch on this issue would be for the designer to pickup the delete rule from the database's foreign key delete rule.

One point more, IMHO there are too many properties affecting the same thing - IsNullable, IsDependent and RemovalBehavior. Personally I found it not really straightforward even without the newly added RemovalBehavior. It would be clearer if only one property set the delete rule - i.e. this new Removal Behavior, although I do suspect a possible backward compatibility issue for LS, possibly it could be used in logic only if existing in the model (and of course it should be validated that IsNullable = false and RemovalBehavarior = SetNull is not possible).

I.e. currently if my association has IsNullable = false, IsDependent=false (default), RemovalBehavior=SetNull (default) children would get deleted automaticaly on parent delete, although by looking at the properties IsDependent and RemovalBehavior are saying something else. And as I said, even without RemovalBehavior, having several flags by which combination you are specifying an action for me is a little confusing.
I know you are probably aware of this and tried to make a solid solution for new feature, just trying to give some customer feedback.