DELETE IN SQL WITH WHERE STATEMENT NO ID REQUIRED

Question

I have ZIP table with a new OP field with DEL information to delete.

When I perform the instruction:

DELETE FROM cepdb.log_bairro where BAI_OPERACAO = 'del';

Returns:

2 Cannot delete or update a parent row: a foreign key constraint fails (`cepdb`.`log_faixa_bairro`, CONSTRAINT `LOG_FAIXA_BAIRRO_FK` FOREIGN KEY (`BAI_NU`) REFERENCES `log_bairro` (`BAI_NU`) ON DELETE RESTRICT ON UPDATE RESTRICT) SQL2.sql 1 1 

How to delete?

Answer 1

The error is stating that there is a foreign key connection to a primary key of a table called LOG_FAIRO.

To solve this problem it is necessary to delete the information containing the same BAI_NU (which is a key primary) table LOG_FAIRO so that when the exclusion is performed in the table LOG_BARO this type of error does not occur.

In short, delete first the BAI_NU of the table of LOG_FAIRO, then delete from the table LOG_BARO, you can solve this problem by running the following commands:

--PRIMEIRO COMANDO (UTILIZEI O SUBSELECT)
DELETE FROM cepdb.log_faixa_bairro fb 
WHERE fb.BAI_NU IN (SELECT lb.BAI_NU 
                    FROM cepdb.log_bairro lb 
                    WHERE lb.BAI_OPERACAO = 'del');

--SEGUNDO SEGUNDO
DELETE FROM cepdb.log_bairro WHERE BAI_OPERACAO = 'del';