How to delete data from a table with dependencies in other tables

Question

I need to delete data from a table that has dependencies on other tables. For example, so that I can delete a data from the person table first I have to delete a dependency that exists in the credential table that in turn has dependency on another table and so repeats with other 3 tables . The problem is, I don’t have just one data to exclude, if I’m not mistaken it’s 1000 to 1500. I think I’ve heard of a way to delete data with one script dependencies. Only now I can’t find I’ve researched several places but I can’t find.

Answer 1

Disabling and Enabling foreign key constraints. The commands are as follows::

For SQL Server:

-- Desabilita todas as constraints de uma tabela

ALTER TABLE MinhaTabela NOCHECK CONSTRAINT ALL

-- Habilita todas as constraints de uma tabela

ALTER TABLE MinhaTabela CHECK CONSTRAINT ALL

-- Desabilita uma constraint

ALTER TABLE MinhaTabela NOCHECK CONSTRAINT MinhaConstraintDeFk

-- Habilita uma constraint

ALTER TABLE MinhaTabela CHECK CONSTRAINT MinhaConstraintDeFk

---

For Mysql:

SET foreign_key_checks = 0;
// Delete o que tiver que deletar

SET foreign_key_checks = 1;  
// Ative a checagem novamente

SET foreign_key_checks = 0;  
// Delete o que tiver que deletar

SET foreign_key_checks = 1;  
// Ative a checagem novamente

---

citations:

1. Stack Overflow Response (in English)
2. Foreign Key Checks (Mysql reference)

Answer 2

SET foreign_key_checks = 0;  
// Delete o que tiver que deletar

SET foreign_key_checks = 1;  
// Ative a checagem novamente

Answer 3

When creating a relationship between the tables put the on delete and delete update in cascade.

Example:

ALTER TABLE `tab_audiencia` ADD CONSTRAINT `tab_audiencia_ibfk_1`
    FOREIGN KEY (`usuario`) REFERENCES `escala`.`usuarios`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE;

So when the record of the "user" table is deleted the other relationships will be automatically deleted

Answer 4

I know the post is already 1 year old, but as we always use them as reference, I would like to make a warning.

The idea of the Gypsy is very punctual and should be used with extreme care. Shutting down constraints is not a good practice, mainly using the add-on ALL. You do not know which constraints have been shut down and therefore do not know which tables are involved.

If you leave a "daughter" table without being cleaned it will be with "trash" and to help, every time we clean a table it is normal to give a reset in the sequences. There you have seen. There will come a time when our sequence will reach old records and mix with new information. You can see where this is going.

This post is not a criticism to its author, it is just a recommendation of care to its use.

Answer 5

When you manipulate data from a database, it is assumed that you know the database, or you would not know what you are doing.

Disabling the constraints temporarily solves? I don’t think so because then you’ll have to call back then anyway you might not have corrupted the record linkage.

That is, you don’t escape knowing what you’re doing, you need to know relationships beforehand and you need to know the implications of deleting a record with dependencies.

The option that makes the most sense to me is delete the records in the order of their dependencies, first the children and then the parents, instead of pretending to ignore these dependencies.

You can still do the mass deletion: delete the 1500 children records, then the 1500 parent records and then their parents and so on.

If deleting dependencies when deleting a parent is a standard procedure for these tables, then I get the Math option: change the table so as to automatically and permanently delete dependencies.

Now, if you really don’t care about the data

If you are for example using a test base may be applicable from time to time to quit deleting records and destroying dependencies as if there were no tomorrow.

In this case an option is a script that retrieves the table metadata and traverses the dependencies by doing the deletion in the correct order.

This script can be a client-side break code in the language of your choice, or a stored Procedure.