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

7

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 which in turn has dependency on another table and thus repeats itself with 3 other tables. The problem is: I do not have just one data to delete, if I am not mistaken it goes from 1000 to 1500. I think I had heard of a way to delete data with dependencies with a single script. Only now I can not find it I've researched in several places but I can not find it.

    
asked by anonymous 29.09.2014 / 23:41

5 answers

3

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

quotes:

  • Stack Overflow Response
  • Foreign Key Checks
  • 29.09.2014 / 23:44
    4
    SET foreign_key_checks = 0;  
    // Delete o que tiver que deletar
    
    SET foreign_key_checks = 1;  
    // Ative a checagem novamente
    
        
    29.09.2014 / 23:55
    3

    When you create a relationship between tables put on delete and delete update into 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 you delete the record of the "user" table, the other relationships will be deleted automatically

        
    19.03.2015 / 19:04
    2

    I know the post is already 1 year old, but as we always use them as a reference, I'd like to make an alert. The idea of Gypsy is very punctual and should be used with extreme care. Turning off constraints is not a good idea, especially using using the "ALL" plugin. You are left without knowing which constraints have been turned off and therefore you do not know what tables are involved. If you leave a table "daughter" without being cleaned it will be "garbage" and to help, every time we clean a table is normal to reset the sequences. Already seen. there comes a time when our sequence will reach old records and mingle the new information. Already know where this will go. This post is not a criticism of its author, it is only a recommendation of care when using it.

        
    07.10.2015 / 15:08
    0

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

    Disable the constraints temporarily resolves? I do not think then you'll have to call back then anyway you can not have corrupted the records relationship.

      

    That is: you do not escape knowing what you are doing, you need to know the relationships beforehand and you need to know the implications of excluding a record with dependencies.

    The most meaningful option for me is delete the records in the order of your dependencies, first the children and then the parents instead of pretending to ignore these dependencies.

    You can still bulk delete: delete the 1500 child records, then the 1500 parent records and then the parent records, and so on.

    If deleting dependencies when deleting a parent is a default procedure for these tables, then I have the Math option: change the table to make the exclusion of dependencies automatic and permanent.

    Now, if you do not really care about the data

    If you are for example using a test database it may be applicable from time to time to exclude records and destroy dependencies as if there were no tomorrow.

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

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

        
    07.10.2015 / 15:37