Home / Questions / 504753
Accepted
Daniel Rivas
Asked: 2022-05-17 15:38:48 +0800 CST 2022-05-17 15:38:48 +0800 CST

Drop a table with MySQL foreign key

  • 772

I need to give DELETE FROM inventariothe inventory table a so that it deletes the content, and after this do a INSERT INTO inventario SELECT * FROM inventario_temp, but I am already using some products in my invoice table, so it gives me an error:

Cannot delete or update a parent row: a foreign key constraint fails ( bdddistribuidoraard. ventas_auxiliar_ard, CONSTRAINT ventas_auxiliar_ard_ibfk_1FOREIGN KEY ( VentaDetalle_IdProducto) REFERENCES inventario_ard( IdProducto))

I would like to see something simple, but I haven't been able to do it, which is to delete all the content, and then give it an insert from the temporary table, quite simple yes, but it hasn't worked, thanks in advance for your help.

mysql

2 Answers

  1. Best Answer

    You can disable FOREIGN_KEY_CHECKS .

    SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE TABLE nombre_de_la_tabla; 
SET FOREIGN_KEY_CHECKS = 1;

    I recommend that before setting the flag back to 1, you truncate all related tables so as not to lose data integrity and maintain the relational logic of the database engine.

    • 3

  2. first of all, using a DROP TABLE means deleting the entire table with its structure, to delete the content and not the structure, TRUNCATE is used, if mysql restricts the deletion it is because it is a mechanism to ensure data integrity, since that is tied to the invoices, one option is to do a logical deletion, that is, create a column and add a 1 or a 0 that will be interpreted as deleted or not deleted, and in the queries, that validation is added to the WHERE, is the proper way. If pure deletion is required, the foreign key constraint must be removed and left as one more column, but data integrity is not ensured, nor is cascade deletion, so there are risks.

    • 2