Query about delete rule

Query about delete rule


This is just ordinary question but I felt need to ask.

If we set Delete Rule to Protect, it doesn't allow me to delete the record and gives error at the time of deleting the record. But if we set Delete Rule to Ignore, it allows us to delete.

By default platform sets Delete Rule to Protect but later we need to change it to Ignore in order to Delete the Record.

Does it really affect anywhere in application changing Delete Rule to "Ignore".? I had gone through documentation several times but not able to get everything properly. Whenever I create Foreign Key, I set Delete Rule to Ignore in order to avoid any problem.

Can someone please provide me more explanation about Delete Rule?

Thanks and Regards,
Suraj Borade
The intent of the Delete Rule is to preserve referential integrity within the data.  Let's say you have a user that has created a record in some entity and that entity has a foreign key to the User entity.  If you set the rule to ignore and you delete the user, any records that had that user's foreign key are now 'orphaned', as they have foreign key values that don't point to an existing record, since it was deleted.

So, in your case, you need to know why you are deleting records that are pointing to other data.  There certainly are some rare cases where setting ignore is the right thing to do but normally you would delete all the child records first so you don't have 'orphans'.

Part of the confusion might also be because you have the entity Sites (shown in the image) which has a foreign key to an entity Site.  Entity names are usually singular and in fact Outsystems counts on that to do some of it's Intelliwarp stuff.  Do you really need Site and Sites?

Hope this helps,
Hi Curt,

Thanks a lot for such helpful information.

I have shown two different entities as Site and Sites.
This entity store basic information about site such as CountryName, IsActive and IsDeleted and it is used everywhere in all  ohter eSpaces.

We have requirement, where user want to add more information about that Site as shown in Entity. I could not think of another name since I need to add one more tab as "Sites" in my existing application.

Now I want to delete Record from "Sites" table and it is used in another table called "Sites_Device". Here I maintaining relationship between Sites and Device i.e. for One site I want to save multiple devices and when I am trying to delete record from Sites table it's giving an error about SitesId from Sites_Device table. 

Now I got what I am doing wrong. Before deleting record from Sites table, I should delete all records from Sites_Device table which refer to SitesId so there won't be orphan records in Sites_Device table. Correct?

Thanks and Regards,
Suraj Borade
Hi Curt and Suraj

I am just investigating this as database Propagation Constraints are exactly that constraints to preserve the integrity of your data and is the corner stone of Referential_integrity.

I found this as I was investigating DB performance and could not quite believe my eyes when reading the following as 'advice' as imo it is cowboy and defeating the purpose of using a relational database Performance_Best_Practices_-_Data_model#Use_of_the_Delete_Rules_constraints_in_Entities 

Suraj ignore Outsystems on this one and use the limited DB functionality that has been exposed to Outsystems modeller.

My growing observation is Outsystems love their UI.

Thanks and Regards,

I fully agree with George. "But in fact while the ignore rule leads to data inconsistency when the application is not deleting all that it should, the protect rule will lead to a database error which while being easier to detect, results in an equally bad end user experience." - eerr... yeah, but "user experience" is not all there is in life... I'd rather have data be consistent, and all...

The best practice in my opinion:

1. ALWAYS use "Protect".

2. NEVER directly call "Delete" from code.

3. ALWAYS wrap your "Delete" functionality in an Action in a Data Layer, which will then look at all referencing records and either set them to a "safe" ID value or remove them as well as the business rules allow.


* "Fail fast/fail hard" from the Protect rule.

* You control what is actually being deleted or not.

* You can detect conditions where the record should not be deleted and provide a user-friendly error message.

Bottom line: relying on the "delete" rule is simply bad behavior because it leads to "mystery meat" operation, the user has no way of knowing that deleting record A from entity X will result in record M in entity Z to evaporate. That is HIGHLY undesireable behavior from the user's point of view. Plus it is a performance killer. "Ignore" is EQUALLY BAD (though in a different way!) because instead of removing data, you just can't trust it! For example, if Attribute "ContactId" is "mandatory" it is not unreasonable for someone in the UI layer to use "GetContact(Id).Record.Name" to do something... only to have it blow up because there is a value that does not exist in the DB. "Ignore" may be *fast* but it leads to a very, very, VERY bad DB situation!

No, "Protect" is the only sane choice, except in very rare cases, such as when performing massive data migrations, updates, etc.


Couldn't agree more!

Interesting stuff.

I don't agree with it 100% but I see where it's coming from and perhaps it's even better when I think about it some more :)

I do wish I need to set the rule instead of having a default.

too many time I recheck the model and see the issue, I fix it, cannot upgrade the database due to existing relations. Need to create an update query first, deploy everywhere, fix the rule, deploy again...  

Great feedback and exactly what I am after.

James said::"the user has no way of knowing that deleting record A from entity X will result in record M in entity Z to evaporate"
  • I have always tried to alert the user first but previously have used commitment control to test before (can this be explicitly implemented in Outsystems?)
  • I want to use the data centric solution of cascading delete instead of laboriously doing programmatic deletes. So WHEN I say DELETE I mean DELETE but using commitment Control or pre queries to determine the WHEN

J. said "Too many time I recheck the model and see the issue, I fix it, cannot upgrade the database due to existing relations. Need to create an update query first, deploy everywhere, fix the rule, deploy again...  "
  • EXACTLY people often complain about constraints as it is hard to re-engineer a relational table to use constraints after not using them...just ask the whole of the IBM DB2 community...which is why in my new home (outsystems) I can't stress the importance of understanding data modeling FIRST and not just throwing together these tables to get on to the 'fun' ui stuff as the DB is 'fun' but if incorrectly done nearly renders a whole application useless after version 1.
I also use delete rather than protect as they both have the same performance penalty...the protect you would have hoped would have been less but is relatively the same as delete which both are far greater then ignore.

But I would say James Protect and my Delete are actually the same as they both rely on you pre checking. Datacentrically using DBMS commintment control was much much easier than writing pre-check queries. As you run the DELETE under commitment control first, IF it fails you roll back! Done, then ask the user are you sure...then delete anyway with out commitment control....but really are you ever going to want to leave orphaned records which is the issue I have with the 'advice' given about this whole issue. Protect your data.

Delete may come in handy if you have a simple datamodel. We have tables that are referenced as foreign keys in dozens of other tables.

Hi Kilian,

Of course you have tables that are referenced as FK in dozens of other tables! you are using a relational table and the reason why you need to use these constraints as you will have dozens of tables with orphaned records!

It is the opposite of what you say imo, a simple model you can get away with a programmatic solution to cascade deletes but more complex data models is when you use the data centric solution of  DBMS and delete constraints as you don't have the time to code everything. Polar opposite view on your comment. 

What I mean is, that in simple data models, it's easier to programmatically delete referencing records than with complex models. However, it's also easier to determine what happens with a delete rule, as there aren't that many tables to track.

Also, we've had situations in which the database (SQL Server in our case) just gave up and produced an error, as the delete rules proved to complex for it.

George -

The OutSystems development model supports soft deletes well. I rely heavily on them with the exception of:

* Data that must be complete removed for legal/regulatory/security/etc. purposes.

* Purging routines that deliberately remove data at scheduled intervals to keep the DB small.

* Records that will never have references made to them, and are totally irrelevant to the business rules, we'll never need to see change history, etc.

I really prefer soft deletes in most cases. The only real downside is performance, and I find that for a system where this is a concern, we can talk about purging.


Hi Kilian,

"simple data models, it's easier to programmatically (this is not by defintion Propagation Constraints) using delete referencing records than with complex models." Exactly. And that is why you use a Datacentric (Propagation Constraints ) solution on complex models.

" also easier to determine what happens with a delete rule, as there aren't that many tables to track." Again the tracking is done datacentrically, you use the dbms system to do it not programmatically writing querys etc and manually keeping track, you use the datacentric solutions avaliable.

Programming this sort of referential integritity is best pushed into the database tier and not some layer above that relies on the developer working out which table and checking it, then checking another and then checking another......you will lose at this method and why datacentric implmentations are far easier as the people writing the DBMS are guaranted to be far brighter than 99% of us. Sorry.

Harness the database of choices datacentric functionality than writing it yourself. This in my expereince is the enterprise way.


James thanks for the heads up about soft deletes as I have already questioned the whole support of historic data in outsystems as it seems again quite code intensive to make each entity historic which is a common requirement of the enterprise.  http://www.outsystems.com/forums/discussion/2699/keep-history/

J, James, Kilian = ALL your opinions are very welcome and the problem I have is I am on my own and have no one to ask and hate the idea of using a low code tool and preaching about low code but when it comes to using it using it in a crap way which reallly defeats the whole purpose and WE can can not afford to give 3GL any excuses to humour us, as I am very very very bored of that