Servoy Forum

Hi

A Datamodelling Tool allows for relation(ships) - if it's fully supportive - 4 actions to model for a relation(ship):
- Cascading Delete
- No Action
- Deny
- Nullify

With Servoy, Cascading delete, No Action, and Deny, can be implemented with the help of the 3 checkboxes available in the Options part of the relation.
But Nullify, which means: Set the foreign key column(s) to null if the record on the 1-side is deleted, doesn't exist in Servoy ( Servoy 4 is my reference).

My request is to add this functionality, i. e. to include, for example, a checkbox named Nullify (see Note 1). This would greatly reduce coding effort, as Nullify has to be done by JavaScipt coding right now. The relation already "knows" which column(s) are affected and could easely nullify the foreign key column(s) if this option is set.

Best regards, Robert

Note 1) I say checkbox as a quick and dirty solution for the current state (display) of these options. The reason for this statement is that I think the current grouping is not very well done, as it's a problem of OR's and AND's, how they can be applied. And the current checkboxes don't reflect that. Only a few combinations of the 3 checkboxes are useful.
I didn't think in depth about it but assume a better arrangement would be of checkboxes and radio buttons to reflect what is when useful.

Good request Robert,

I totally agree with you

Martin

Thanks, Martin

make a feature request for this.

Problem i see with this is do people also expect that the whole data really stays in sync?
i mean i have this:

orderlines has a productid -> product.productid

now i have many orderlines pointing to the same productid

then you do through one of those

orderlines record.orderlines_to_product.deleteRecord(1)

then if we have nullable we could null the productid in that orderlines record
But what about the rest? the 10K orderlines records that also have a reference to that productid?

if so of we have that and we do just in a none related foundset based on products deleteRecord(1) what should happen then?
so i think nullable can quickly result in inconsistent data in the database, the database itself should also handle these events.

jcompagner wrote:make a feature request for this.

Problem i see with this is do people also expect that the whole data really stays in sync?
i mean i have this:

orderlines has a productid -> product.productid

now i have many orderlines pointing to the same productid

then you do through one of those

orderlines record.orderlines_to_product.deleteRecord(1)

then if we have nullable we could null the productid in that orderlines record
But what about the rest? the 10K orderlines records that also have a reference to that productid?

if so of we have that and we do just in a none related foundset based on products deleteRecord(1) what should happen then?
so i think nullable can quickly result in inconsistent data in the database, the database itself should also handle these events.

Johan,

In my opinion this nullable feature should only work on:

On delete product, put NULL value in the child records (like orderlines)

Now we have the following options:

Allow parent delete, when having related records
Delete related records

I would add an extra option:

Nullify related records

Of course Delete related records and Nullify related records can not be selected at the same time

The case you describe

Code: Select all

record.orderlines_to_product.deleteRecord(1)

shoud update also the other 10K of orderlines. I just hope that there is nobody that uses a construction like above

Martin

Hi Johann

jcompagner wrote:make a feature request for this.

I have done a feature request.

jcompagner wrote:Problem i see with this is do people also expect that the whole data really stays in sync?
i mean i have this:

orderlines has a productid -> product.productid

now i have many orderlines pointing to the same productid

then you do through one of those

orderlines record.orderlines_to_product.deleteRecord(1)

then if we have nullable we could null the productid in that orderlines record
But what about the rest? the 10K orderlines records that also have a reference to that productid?

if so of we have that and we do just in a none related foundset based on products deleteRecord(1) what should happen then?
so i think nullable can quickly result in inconsistent data in the database, the database itself should also handle these events.

Nullify means set the foreign key column(s) to null if the 1-side record is deleted (the master record of a master - details situation). So you describe above a rare situation, where the master record is being deleted from a detail record. But nevertheless, the effect is, you just delete the master record. And the nullify rule says, if you delete the master record, nullify the details records foreign key.
The answer for your above described situation is: Nullify ALL details records foreign key for that relation! (in your example nullify all the foreign keys (of the p.product_to_orderlines.o relation) of the 10k orderlines.

The question is not how the master record (productid) is deleted, but when it is deleted, nullify the foreign key.

Best regards, Robert

PS1: If you don't delete the foreign key (currently manually with code developed by the developer) but delete the master record, there are 10k (in your example) of WRONG foreign key data in these records, which of course death data! It even can led to wrong results - for example if some code checks for the existance of the relation by checking if data exists in the foreign key.

PS2: Your example of orderlines record.orderlines_to_product.deleteRecord(1) is very rare, as the detail record normaly does NOT have the right to delete the master record. An ordinary exception to this may be the case when the detail record is the last record to exist, then, if the master - detail relation(ship) is defined mandatory, the master record has to be deleted as well.