Error deleting FK

Question

Error deleting FK

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

I have a table called:

solicicaoemprestimo codemprestimo, dataemprestimo, horaemprestimo

And another table called:

loaned, codempresented, dateprice, timeprice, fk (requestpayment_codemprestimo)

and I 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';

error giving:

Error Code: 1451. Can not 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'm going to delete the table on my application because my DVD was rented, it does not leave, but I'd like to delete it because this item will no longer be used because the DVD was already rented ... If someone can help me right away I appreciate it ...

mysql

asked by anonymous 04.06.2015 / 17:46

2 answers

Returning HTML Fragment with Ajax Align left and decrease distance between controls with bootstrap

score 1 · Answer 1

This is intentional - the idea of creating a foreign key is to prevent a column that references an ID from another table from storing an invalid value. If the query you want to do is allowed, a row would have a value of 13 for the solicitacaoemprestimo_codemprestimo column even though there is no row 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 active requests? Consider rethinking the design of your database and leave all the requests, forever, in this table - if you want to filter only requests that have not yet been answered, you can always do

…
WHERE codemprestimo NOT IN (SELECT solicitaremprestimo_codemprestimo FROM emprestados)

(but it would be important for you to no longer repeat the columns dataemprestimo , horaemprestimo in the emprestados table)

score 0 · Answer 2

Complementing the answer from ctgPi you could create a Boolean field to tell if the request has already been rented. For example:

ALTER TABLE solicitacaoemprestimo 
ADD alugado BOOLEAN

And in your case it would be:

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

Instead of DELETE .