Problem persisting: "Can not add or update a child row: a foreign key constraint fails ..."

Question

Problem persisting: "Can not add or update a child row: a foreign key constraint fails ..."

Navigation

#1 by (3 votes)

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

Technical

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()
}

Technical

@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

HibernateContactData

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();
    }

}

HibernateTechnicalData

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

HibernateTechnicalDAOTest

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

Configuration

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 while persisting a Technician object, because every time I try to persist it, the exception described above is thrown, but in short, the cause might be this:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can not add or update 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).

Contact and Address instances persist normally.

The cascade attribute of annotations as @OneToOne can take on several values, but in my case when I assign CascadeType.PERSIST to the cascade attribute, it simply does not work. I use JPA in entities and Hibernate in DAO's.

Is there anything I have failed to do or is this a malformed Hibernate problem?

mysql java hibernate jpa

asked by anonymous 14.07.2014 / 03:40

1 answer

Show database image for each usercontrol What is the difference and advantages of SDL and Unity3D?

score 3 · Accepted Answer

I solved the problem by analyzing the code of the tables after their creation, and found that in one of the tables a primary key SMALLINT (5) was created and in another referenced table a SMALLINT (6) was created. Thus, changing the table with the primary key SMALLINT (6) to SMALLINT (5) returned to normal operation.

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