Início / dba / Perguntas / 49077
Accepted
James
Asked: 2013-09-02 12:39:15 +0800 CST

MySQL: atualizar em vez de excluir se houver restrição de chave estrangeira?

  • 772

Eu tenho uma pergunta um pouco estranha. Eu sei sobre inserir na atualização de chave duplicada. Minha pergunta é: existe algo semelhante para exclusões que falham devido a restrições de chave estrangeira?

Por exemplo:

excluir da tabela1 onde value='something';

Porém, a tabela2 possui uma chave estrangeira que depende do valor que desejo excluir na tabela1, portanto, a exclusão falha. Eu gostaria de fazer algo assim:

excluir da tabela1 onde value='something' na chave estrangeira falha atualizar some_other_value='something else';

Sei que parece muito estranho, mas tenho um bom motivo para fazer isso (sem entrar em detalhes, tem a ver com o controle de versão de dados históricos que não podem ser destruídos caso um valor seja referenciado em outro lugar). descobrir como fazer isso com mais de uma consulta, é claro, mas gostaria de fazer isso em uma única consulta, se possível. Tenho certeza que não é possível, mas gostaria de perguntar antes de desistir :)

Obrigado!

mysql foreign-key

1 respostas

  1. Best Answer

    Você não pode fazer isso diretamente, pois não há como (que eu possa pensar) alternar UPDATEou DELETEda mesma consulta ... geralmente uma consulta pode fazer apenas um tipo de operação CRUD ( ON DUPLICATE KEY UPDATEsendo uma exceção a isso).

    Você poderia, no entanto, fazer com que "parecesse" uma única consulta, com um procedimento armazenado, onde você configuraria um CONTINUE HANDLERpara o erro de chave estrangeira previsto. O manipulador interceptará o erro e pode ser usado para definir uma variável que você pode testar para ver se encontrou o erro e, portanto, também precisa tentar a consulta de atualização.

    DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

    Isso tentará excluir; se encontrar um erro de chave estrangeira, ele tentará a atualização (e o erro de chave estrangeira será suprimido). O procedimento retornará um conjunto de registros com o número de linhas afetadas por cada consulta e se ocorreu o erro de chave estrangeira. Se o "input_value" não for um INTou a WHEREcláusula for mais complexa, você precisará modificar a estrutura de acordo.

    O problema potencial que vejo aqui é que, se a cláusula where for muito ampla e corresponder a algumas linhas que causariam um erro de chave estrangeira e outras linhas que não, a consulta não tratará as linhas individualmente. Isso poderia ser feito por um procedimento mais sofisticado que usasse um cursor para localizar as linhas na cláusula where e tentasse excluí-las ou atualizá-las individualmente por chave primária após identificá-las. Essa abordagem seria menos eficiente, mas mais precisa.

    • 3

relate perguntas