Error while deleting FK

Question

I have a table called:

soliciting codemprestimo, dataemprestimo, horaemprestimo

And another table called:

loan, co-loan, loan, loan, loan, loan, loan)

and used this command:

INSERT INTO emprestados (dataemprestimo,horaemprestimo, solicitacaoemprestimo_codemprestimo);

SELECT data emprestimo, horaemprestimo, codemprestimo FROM solicitacaoemprestimo WHERE codemprestimo = '13' 

DELETE FROM solicitacaoemprestimo WHERE codemprestimo = '13';

mistake you’re making:

Error Code: 1451. Cannot delete or update a Parent Row: a Foreign key Constraint fails (trabalhoclubedodvd.emprestados, CONSTRAINT fk_emprestados_solicitacaoemprestimo1 FOREIGN KEY (solicitacaoemprestimo_codemprestimo)

Only, at the time that I will delete in the table I request it because my dvd has been rented, it does not let, but I would like to delete because this item will no longer be used because the dvd has already been rented... If anyone can help me right away I’d appreciate it...

Answer 1

This is intentional - the idea of creating a foreign key is to prevent a column that references an ID in another table from storing an invalid value. If the query you want to do were allowed, a row would be 13 for the column solicitacaoemprestimo_codemprestimo although there is no line in solicitacaoemprestimo with codemprestimo equal to 13.

The motivation to delete the corresponding line in solicitacaoemprestimo is that the semantics you have in mind for this table is that it will only save requests active? Consider rethinking your database design and leave all requests forever on this table - if you want to filter only requests that haven’t been met yet, you can always do

…
WHERE codemprestimo NOT IN (SELECT solicitaremprestimo_codemprestimo FROM emprestados)

(but then it would be important you no longer repeat the columns dataemprestimo, horaemprestimo on the table emprestados)

Answer 2

Complementing the response of ctgPi you could create a boolean field to inform if the request has already been rented. For example:

ALTER TABLE solicitacaoemprestimo 
ADD alugado BOOLEAN

And in your case:

UPDATE solicitacaoemprestimo SET alugado = 1 WHERE codemprestimo = '13';

Instead of DELETE.