SQL - Perform the removal of two rows in separate tables connected with Foreign Key

Question

Good afternoon, I have a little doubt on a personal project of mine.

I own the tables request and itens_request which are connected by foreign key.

As an example to simplify my situation:

Requested table

Columns: CODE(PRIMARY) & NAME

Table itens_request

Columns: CODE(PRIMARY) & PRODUTO_CODIGO(FOREIGN)

Now, my goal is to delete a row from the product table, which can only be deleted after deleting lines from the itens_product who are connected by Foreign key. However, I want to delete it in just one SQL Query, without having to use two DELETE in a row. However, when trying to do this with INNER JOIN, returns me the following error:

Cannot delete or update a Parent Row: a Foreign key Constraint fails (revenda.itens_pedido, CONSTRAINT ITENS_PEDIDO FOREIGN KEY (PEDIDO_CODIGO) REFERENCES pedido (CODIGO))

The error is caused by me trying to remove the row from the table request without having happened the exclusion of the line that uses the CODE of the application as a reference in Foreign key.

The SQL Query I used was:

DELETE itens_pedido, pedido FROM pedido INNER JOIN itens_pedido ON pedido.CODIGO = itens_pedido.PEDIDO_CODIGO WHERE pedido.CODIGO = 2

If anyone knows any way to solve this problem, comment here, I thank you.

Answer 1

The only way to avoid the error of violation of Foreign Key is undoing the link between the records. There are only two ways to do:

1. First deleting child records:

DELETE FROM itens_pedido WHERE PEDIDO_CODIGO = 2;
DELETE FROM pedido WHERE codigo = 2;

2. Using the option DELETE CASCADE in the Foreign Key definition. Thus, the table records itens_request will be deleted automatically when the table record request be excluded.

ALTER TABLE itens_pedido ADD CONSTRAINT fk_itenspedido_pedido (PEDIDO_CODIGO) 
        references pedido(CODIGO) ON DELETE CASCADE;