9
 Followers
72
 Likes

Script - Delete All Records

Database
New

I would like to right click on a Entity in a non-production Environment and generate one script with delete in cascade to clear all the records from that entity and avoid the error "The DELETE statement conflicted with the REFERENCE constraint".

Created on 10 Oct 2018
Comments (14)

Good idea, will be big help. 


Changed the category to Database


Nice idea.  Either DeleteAll and DeleteAllCascade could be very useful for t projects.

In case of DeleteAllCascade it could be similar to Synch Patterns on the mobile side. If you select this option an action could be created and allows us to customize it...




Good!

We was need to proceed this operation and make it manually.

Sounds good. That's a great Idea. Let me know if I understand well. Is it similar a scaffolding to generate a script based on the relationship between the entities database (foreing keys)?

I never understood why we could not do a trunc table :(


Can be useful indeed.

That would be really helpful!

Finally someone said it !! Thank you ! Looking forward to see this in the next build !
Keep up with the great ideas Carlos! 

Not quite what you want but remember you can always do an advanced query and do a delete or truncate table. When developing we have a "hidden" page that we add delete or clear functions to allow for full reset of data. The referential integrity for identifier links get in the way though if you leave the "Delete Rule" at the default of protect meaning you need to either change that rule to "Delete" to allow cascade delating or do you manual deletes in the correct order.

One thing to remember is that SQL does not let you do a Truncate table if there are any foreign keys with protect pointing to that table.



Of cause if it is just the "The DELETE statement conflicted with the REFERENCE constraint" error you want to avoid then simply setting the delete rule to “Delete” will automatically do a cascade delete.



Hi there John.

But if you change the "Delete Rule" of your Entity, this change go to production, or ever you want to delete the records, change this property to delete and remember after returning these properties.

My Idea is for create the script for delete in cascade. You're correct in relation the error about FK, but if you go generate the script or delete in cascade, you can disable this FK in your script, delete all registers without error and in script enable again the FKs

That's why my Idea is only for environments non production, because in production the entitys don't have delete in cascade configured.


Regards,

Carlos Alfradique.

Not always, it's not good programming to rely on referential checks to prevent you from accidently deleting data. I’ve lost count of the number of times I’ve seen an application throw a raw SQL key violation on deletes or edits to an end user, not nice. Good practice is for all deletes to check for child data and then make a decision on a) Allow the delete, b) do something with the child data such as move to another parent, or c) present a "friendly" message to the user that they cannot delete. 

In our case we employ a system where in normal operation we never actually delete records, rather we set them as inactive and it is only when we "really" want data removed along with all its children that we do a real delete at which point the referential integrity becomes quite a pain.




That would be really helpful! This will save lot of time as are handling this manually

views
596
Followers
9