Error deleting data in Mysql by Java

2

In the database I have three tables, where one of them holds the foreign keys. Error Displayed: Error: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can not delete or update parent row: a foreign key constraint fails ( db_teste . tbl_Relacao , CONSTRAINT tbl_Relacao_ibfk_1 FOREIGN KEY ( primeira_pessoa ) REFERENCES pessoas (% with%))

Relative class:

public class Parentes {
private int id_parentes;
private int nivel;
private String parentesco;
//getters e setters

Class person:

public class Pessoa {
private int id_pessoa;
private String sexo;
private String nome;
private String usuario;
private String senha;
//getters e setters

Relationship class:

public class Relacao {
private int id_relacao;
private int primeira_pessoa;
private int segunda_pessoa;
private int tipo_relacao;
private Parentes parente;
private Pessoa pessoa1;
private Pessoa pessoa2;
//getters e setters

Delete relatives:

public boolean delete(Parentes parentes){
    String sql = "DELETE FROM tbl_parentes WHERE id_parentes = ?";
    PreparedStatement stmt = null;

    try {
        stmt = con.prepareStatement(sql);
        stmt.setInt(1, parentes.getId_parentes());
        stmt.executeUpdate();

        return true;
    } catch (SQLException ex) {
        System.err.println("ERROR: "+ex);
        return false;
    }
    finally{
        ConnectionFactory.closeConnection(con, (com.mysql.jdbc.PreparedStatement) stmt);
    }
}

Delete of person DAO:

    public boolean update(Pessoa pessoa){
    String sql = "UPDATE pessoas SET nome = ?, sexo = ? WHERE id_pessoas = ?";

    PreparedStatement stmt = null;

    try {
        stmt = (PreparedStatement) con.prepareStatement(sql);
        stmt.setString(1, pessoa.getNome());
        stmt.setString(2, pessoa.getSexo());
        stmt.setInt(3, pessoa.getId_pessoa());
        stmt.executeUpdate();

        return true;

    } catch (SQLException ex) {
        System.err.println("Erro: " + ex);
        return false;
    }finally{
        ConnectionFactory.closeConnection(con, stmt);
    }
}    

Delete from relationAO:

    public boolean delete(Relacao relacao){
    String sql = "DELETE FROM tbl_Relacao WHERE id_relacao = ?";
    java.sql.PreparedStatement stmt = null;

    try {
        stmt = con.prepareStatement(sql);
        stmt.setInt(1, relacao.getId_relacao());
        stmt.executeUpdate();

        return true;
    } catch (SQLException ex) {
        System.err.println("ERROR: "+ex);
        return false;
    }
    finally{
        ConnectionFactory.closeConnection(con, (com.mysql.jdbc.PreparedStatement) stmt);
    }
}

I'm using JUnit to do the test, here's the code below:

   public void deleteDAO() {
    Pessoa pessoa = new Pessoa();
    PessoaDAO pessoaDAO = new PessoaDAO();

    Relacao relacionamento = new Relacao();
    RelacaoDAO relacionamentoDAO = new RelacaoDAO();

    Parentes parentes = new Parentes();
    ParentesDAO parentesDAO = new ParentesDAO();

    //pessoa.setId_pessoa(3);

    //Qualquer valor vai apresentar o mesmo erro
    pessoa.setId_pessoa(c.getId_pessoa());

    parentes.setId_parentes(c.getId_parentes());

    relacionamento.setId_relacao(c.getId_relacao());


    if(pessoaDAO.delete(pessoa) && relacionamentoDAO.delete(relacionamento) && parentesDAO.delete(parentes)){
        System.out.println("Removido com sucesso!");
    }
    else{
        fail("Erro ao deletar");
    }
}

The problem is that when I try to delete the data an error appears in the table containing the foreign keys, I would like to know what I am doing wrong.

Note: I am a beginner.

    
asked by anonymous 13.05.2018 / 21:32

1 answer

1

As you are getting started, let's go for parts as jack would say ...

1. Do not put entity name in plural:

Ex: Relatives > > Relative

2. Do not put attribute name in java with underline (_):

Ex: personal id> > idPessoa

3. Constraint error when trying to delete

Try changing the deletion order to:

if(relacionamentoDAO.delete(relacionamento) && pessoaDAO.delete(pessoa) && parentesDAO.delete(parentes)){
    System.out.println("Removido com sucesso!");
}

Another solution would be to work with cascade in the bank.

Doubt: The Relatives table relates to which table? In your class this is not clear.

    
15.05.2018 / 14:18