Problem persisting: "Cannot add or update a Child Row: a Foreign key Constraint fails..."

Question

Problem persisting: "Cannot add or update a Child Row: a Foreign key Constraint fails..."

Asked 9 years, 11 months ago

Viewed 8,935 times

2

Tables

Contact

CREATE TABLE IF NOT EXISTS `sgct-database1`.`contato` (
  `contato_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telefone` CHAR(10) NOT NULL,
  `celular` CHAR(11) NULL,
  PRIMARY KEY (`contato_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1

Address

CREATE TABLE IF NOT EXISTS `sgct-database1`.`endereco` (
  `endereco_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `endereco` VARCHAR(120) NOT NULL,
  `numero` CHAR(8) NOT NULL,
  `complemento` VARCHAR(40) NULL,
  `cep` CHAR(8) NULL,
  `bairro` VARCHAR(120) NULL,
  `cidade` VARCHAR(40) NOT NULL,
  `estado` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`endereco_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1

Technician

CREATE TABLE IF NOT EXISTS `sgct-database1`.`tecnico` (
  `tecnico_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(128) NOT NULL,
  `login` VARCHAR(16) NOT NULL,
  `senha` VARCHAR(16) NOT NULL,
  `rg` CHAR(9) NOT NULL,
  `cpf` CHAR(11) NOT NULL,
  `observacoes` VARCHAR(300) NULL,
  `status` CHAR(12) NOT NULL,
  `contato_id` SMALLINT UNSIGNED NOT NULL,
  `endereco_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`tecnico_id`),
  UNIQUE INDEX `login_UNIQUE` (`login` ASC),
  INDEX `fk_tecnico_contato1_idx` (`contato_id` ASC),
  INDEX `fk_tecnico_endereco1_idx` (`endereco_id` ASC),
  CONSTRAINT `fk_tecnico_contato`
    FOREIGN KEY (`contato_id`)
    REFERENCES `sgct-database1`.`contato` (`contato_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tecnico_endereco`
    FOREIGN KEY (`endereco_id`)
    REFERENCES `sgct-database1`.`endereco` (`endereco_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1

Entities

@Entity
public class Contato implements Serializable {

    private static final long serialVersionUID = -5117050430686257801L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "contato_id")
    private Short codigo;

    private String telefone;
    private String celular;

    public Contato() {
    }

    //gets e sets
    //hashcode() e equals()
}

Address

@Entity
public class Endereco implements Serializable {

    private static final long serialVersionUID = 5071505092437076722L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "endereco_id")
    private Long codigo;

    private String endereco;
    private Short numero;
    private String complemento;
    private String cep;
    private String bairro;
    private String cidade;
    private String estado;

    public Endereco() {
    }

    //gets e sets
    //hashcode() e equals()
}

Technician

@Entity
public class Tecnico implements Serializable {

    private static final long serialVersionUID = 1832056502374198975L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "tecnico_id")
    private Short codigo;

    private String nome;

    @Column(unique = true)
    private String login;

    private String senha;
    private String rg;
    private String cpf;
    private String observacoes;

    @ElementCollection(targetClass = String.class)
    @JoinTable(name = "tecnico_permissao", uniqueConstraints = { @UniqueConstraint(columnNames = {
            "tecnico", "permissao" }) }, joinColumns = @JoinColumn(name = "tecnico"))
    @Column(name = "permissao", length = 50)
    private Set<String> permissao;

    @Enumerated(value = EnumType.STRING)
    private Status status;

    @OneToOne/*(cascade = CascadeType.ALL) - Não está funcionando */
    @JoinColumn(name = "contato_id")
    private Contato contato;

    @OneToOne/*(cascade = CascadeType.ALL) - Não está funcionando */
    @JoinColumn(name = "endereco_id")
    private Endereco endereco;

    public Tecnico() {
        permissao = new HashSet<String>();
    }

    //gets e sets
    //hashcode() e equals()
}

Impl DAO’s

Hibernatecontatodao

public class HibernateContatoDAO implements ContatoDAO {

    private Session session;

    public HibernateContatoDAO(Session pSession) {
        this.session = pSession;
    }

    @Override
    public void salvar(Contato pContato) {
        this.session.save(pContato);
    }

    @Override
    public void atualizar(Contato pContato) {
        this.session.update(pContato);
    }

    @Override
    public void excluir(Contato pContato) {
        this.session.delete(pContato);
    }

    @Override
    public Contato carregar(Short pCodigo) {
        return (Contato) this.session.get(Contato.class, pCodigo);
    }

    @Override
    public List<Contato> listar() {
        return this.session.createCriteria(Tecnico.class).list();
    }

}

Hibernateenderecodao

public class HibernateEnderecoDAO implements EnderecoDAO {

    private Session session;

    public HibernateEnderecoDAO(Session pSession) {
        this.session = pSession;
    }

    @Override
    public void salvar(Endereco pEndereco) {
        this.session.save(pEndereco);
    }

    @Override
    public void atualizar(Endereco pEndereco) {
        this.session.update(pEndereco);
    }

    @Override
    public void excluir(Endereco pEndereco) {
        this.session.delete(pEndereco);
    }

    @Override
    public Endereco carregar(Short pCodigo) {
        return (Endereco) this.session.get(Endereco.class, pCodigo);
    }

    @Override
    public List<Endereco> listar() {
        return this.session.createCriteria(Endereco.class).list();
    }

}

Hibernatetecnicodao

public class HibernateTecnicoDAO implements TecnicoDAO {

    private Session session;

    public HibernateTecnicoDAO(Session session) {
        this.session = session;
    }

    @Override
    public void salvar(Tecnico pTecnico) {
        this.session.save(pTecnico);
    }

    @Override
    public void atualizar(Tecnico pTecnico) {
        if (pTecnico.getPermissao() == null
                || pTecnico.getPermissao().size() == 0) {
            Tecnico tecnicoPermissao = this.carregar(pTecnico.getCodigo());
            pTecnico.setPermissao(tecnicoPermissao.getPermissao());
            this.session.evict(tecnicoPermissao);
        }

        this.session.update(pTecnico);
    }

    @Override
    public void excluir(Tecnico pTecnico) {
        this.session.delete(pTecnico);
    }

    @Override
    public Tecnico carregar(Short pCodigo) {
        return (Tecnico) this.session.get(Tecnico.class, pCodigo);
    }

    @Override
    public Tecnico carregarPorLogin(String pLogin) {
        String hql = "select t from Tecnico t where t.login = :login";
        Query consulta = this.session.createQuery(hql);
        consulta.setString("login", pLogin);
        return (Tecnico) consulta.uniqueResult();
    }

    @Override
    public Tecnico carregarPorRg(String pRg) {
        String hql = "select t from Tecnico t where t.rg = :rg";
        Query consulta = this.session.createQuery(hql);
        consulta.setString("rg", pRg);
        return (Tecnico) consulta.uniqueResult();
    }

    @Override
    public List<Tecnico> listar() {
        return this.session.createCriteria(Tecnico.class).list();
    }
}

Test Class

Hibernatetecnicodaotest

public class HibernateTecnicoDAOTest {

    private TecnicoDAO tecnicoDAO;
    private ContatoDAO contatoDAO;
    private EnderecoDAO enderecoDAO;

    @BeforeClass
    public static void setUpBeforeClass() throws Exception {

    }

    @AfterClass
    public static void tearDownAfterClass() throws Exception {

    }

    @Before
    public void setUp() throws Exception {
        tecnicoDAO = DAOFactory.criarTecnicoDAO();
        enderecoDAO = DAOFactory.criarEnderecoDAO();
        contatoDAO = DAOFactory.criarContatoDAO();

        HibernateUtil.getSessionFactory().getCurrentSession()
                .beginTransaction();
    }

    @After
    public void tearDown() throws Exception {
        HibernateUtil.getSessionFactory().getCurrentSession().getTransaction()
                .commit();
        HibernateUtil.getSessionFactory().getCurrentSession().close();
    }

    @Test
    public void testSalvarSucesso() {
        Tecnico tecnico = new Tecnico();
        Endereco endereco = new Endereco();
        Contato contato = new Contato();

        tecnico.setNome("Galisteu da Silva");
        tecnico.setCpf("12345678901");
        tecnico.setRg("112223334");
        tecnico.setLogin("galisteu");
        tecnico.setSenha("123");
        tecnico.setStatus(Status.DISPONIVEL);

        contato.setTelefone("11112222");
        contato.setCelular("981112222");

        endereco.setCidade("São Paulo");
        endereco.setEstado("São Paulo");
        endereco.setCep("12233444");
        endereco.setEndereco("Rua Maria Souza");
        endereco.setNumero((short) 123);

        tecnico.setEndereco(endereco);
        tecnico.setContato(contato);

        enderecoDAO.salvar(endereco);
        contatoDAO.salvar(contato);

        System.out.println("ID_CON: " + contato.getCodigo());
        System.out.println("ID_END: " + endereco.getCodigo());

        tecnicoDAO.salvar(tecnico);
    }
}

Exception

Hibernate: insert into Endereco (bairro, cep, cidade, complemento, endereco, estado, numero) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into Contato (celular, telefone) values (?, ?)
ID_CON1
ID_END1
Hibernate: insert into Tecnico (contato_id, cpf, endereco_id, login, nome, observacoes, rg, senha, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Exception in thread "main" org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:72)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
    at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:490)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:195)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:179)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:214)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:324)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:288)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:194)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:125)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:356)
    at com.sun.proxy.$Proxy15.save(Unknown Source)
    at org.sgct.model.HibernateTecnicoDAO.salvar(HibernateTecnicoDAO.java:25)
    at org.sgct.model.HibernateTecnicoDAO.main(HibernateTecnicoDAO.java:110)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`sgct-database1`.`tecnico`, CONSTRAINT `fk_tecnico_contato` FOREIGN KEY (`contato_id`) REFERENCES `sgct-database1`.`contato` (`contato_id`) ON DELETE CASCADE ON UPDATE NO ACTION)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
    at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeUpdate(PreparedStatementJavassistProxy.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 29 more

Setup

Hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <!-- Configuração para a instância do SessionFactory -->
    <session-factory>

        <!-- Propriedades para o Hibernate -->
        <property name="hibernate.dialect">
            org.hibernate.dialect.MySQL5InnoDBDialect
        </property>
        <property name="hibernate.show_sql">
            true
        </property>
        <property name="hibernate.connection.provider_class">
            com.zaxxer.hikari.hibernate.HikariConnectionProvider
        </property>
        <property name="hibernate.current_session_context_class">
            thread
        </property>
        <property name="hibernate.hbm2ddl.auto">
            update
        </property>
<!--        <property name="hibernate.generator_mappings">
            true
        </property> -->

        <!-- Propriedades para o Pool de Conexões HirakiCP -->
        <property name="hibernate.hikari.dataSourceClassName">
            com.mysql.jdbc.jdbc2.optional.MysqlDataSource
        </property>
        <property name="hibernate.hikari.dataSource.url">
            jdbc:mysql://localhost:3306/sgct-database1?createDatabaseIfNotExist=true
        </property>
        <property name="hibernate.hikari.dataSource.user">
            root
        </property>
        <property name="hibernate.hikari.dataSource.password">
            adm123
        </property>
        <property name="hibernate.hikari.maximumPoolSize">
            10
        </property>
        <property name="hibernate.hikari.idleTimeout">
            30000
        </property>
<!--        <property name="hibernate.hikari.dataSource.cachePrepStmts">
            true
        </property>
        <property name="hibernate.hikari.dataSource.prepStmtCacheSize">
            250
        </property>
        <property name="hibernate.hikari.dataSource.prepStmtCacheSqlLimit">
            2048
        </property>
        <property name="hibernate.hikari.dataSource.useServerPrepStmts">
            true
        </property> -->
<!--        <property name="hibernate.hikari.dataSource.useLocalSessionState">
            true
        </property>
        <property name="hibernate.hikari.dataSource.useLocalTransactionState">
            true
        </property>
        <property name="hibernate.hikari.dataSource.maintainTimeStats">
            false
        </property>
        <property name="hibernate.hikari.dataSource.useUnbufferedInput">
            false
        </property> -->

        <!-- Mapeamento de classes -->
        <!-- <mapping package="org.sgct.model" /> -->
        <mapping class="org.sgct.model.Tecnico" />
        <mapping class="org.sgct.model.Contato" />
        <mapping class="org.sgct.model.Endereco" />

    </session-factory>

</hibernate-configuration>

Questions

I’m having problems persisting an object Technical, because every time I try to persist it, is released the Exception described earlier, but, in short, the cause could be this:

Caused by: com.mysql.jdbc.exceptions.jdbc4.Mysqlintegrityconstraintviolationexception: Cannot add or update a Child Row: a Foreign key Cont strainfails (sgct-database1.tecnico, CONSTRAINT fk_tecnico_contato FOREIGN KEY (contato_id) REFERENCES sgct-database1.contato (contato_id) ON DELETE CASCADE ON UPDATE NO ACTION).

The instances of Contact and Addressee persist normally.
The attribute scade of annotations as @OneToOne may assume various values, but in my case when I assign CascadeType.PERSIST to the attribute cascade, simply doesn’t work. JPA usage on entities and Hibernate on Daos.

Is there something I’ve failed to do or is it Hibernate’s misconfiguration problem?

The Constraint error, I imagine is that your Technical table tries to use a Foreign key that is the primary key in the Contact table, however the value you indicate does not exist. You’ve already checked that?

– Math

2014/07/14 at 10:39
I already checked it, so much so that I put one sysout in the test class to see even if Hibernate after persisting recovered the keys generated by the database to be used in the Technical instance. One thing, when the save() of Ibernate he executes the flush() internally? And using the CascadeType.PERSIST the same error occurs.

– klaytonbrito

2014/07/14 at 17:29

1 answer

Browser other questions tagged java mysql hibernate jpa

You are not signed in. Login or sign up in order to post.

by klaytonbrito • **491** points · Answer 1 · 2014-07-15T19:30:15+00:00

I solved the problem by analyzing the code of the tables after its creation, and found that in one of the tables a SMALLINT(5) key was created and in another referenced table a SMALLINT(6) was created. Thus, by changing the table with Primary key SMALLINT(6) to SMALLINT(5) it returned to function normally.

One thing I learned to avoid problems is to directly define the column size to avoid future problems like this.