Unique key violated when I am doing a delete. Because?


I have a problem that seems silly but I'm skating and I can not solve it. I have a legal application made in Java using JPA 2 with Spring, Spring Data, JSF, etc ... It works very well. The base I'm using is Oracle 10g.

Well now comes the problem, I have two tables. One related to the other. As follows:

Tabela: aplicacoes

 - id (pk);
 - nome;
 - usuario_acesso (unique key);
 - password;

Tabela: sessoes

 - id (pk);
 - token;
 - aplicacao_id (fk para tabela aplicacoes 1xN);
 - data_login;

Well, in this very simple example. I can have 1 application for N sessions. So far so good.

My problem starts when I try to delete an application that already has a session. Of course, I delete all sessions from that application before deleting the application, otherwise FK Violation would occur. But what happens here is that even though I delete all sessions pertaining to that application and then trying to delete the application a unique key violation !

Oras, why would this unique key no delete? It would make sense for this problem to occur when I insert a new record with a duplicate user name. But that's not the case.

The exception that occurs:

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (LOADERS_SCH.UK_9F63GD4XERQ7X8MNPKSKY69V) violated

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187) [hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    ... 117 more

The error occurs precisely when I commit the transaction and not soon after sending the delete.

ISSUE: I'm adding the requested codes in the comments, plus some other things that might help with the answer.


@Table(schema = "loaders_sch", name = "ws_aplicacoes_clientes")
public class AplicacaoClienteVO implements Serializable {

    private static final long serialVersionUID = 3401460653428856555L;

    @SequenceGenerator(name = "id_seq_appcliente", sequenceName = "seq_appcliente", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq_appcliente")
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "nome_aplicacao", nullable = false, unique = true)
    private String nome;

    @Column(name = "usuario", nullable = false, unique = true)
    private String usuarioAcesso;

    @Column(name = "descricao", nullable = true)
    private String descricao;

    @Column(name = "password", nullable = false)
    private String password;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "ws_app_ws", joinColumns = { @JoinColumn(name = "app_id", referencedColumnName = "id") }, inverseJoinColumns = { @JoinColumn(name = "webservice_id", referencedColumnName = "id") })
    private Set<WebserviceVO> webservices;

    @Column(name = "ativo", nullable = false)
    private Boolean ativo;

    //Getters e Setters omitidos


@Table(schema = "loaders_sch", name = "ws_sessoes", indexes = {
        @Index(columnList = "token"),
        @Index(columnList = "app_cliente_id")
public class SessaoVO implements Serializable {

    private static final long serialVersionUID = -7578025167356770714L;

    @SequenceGenerator(name = "id_seq_sessao", sequenceName = "seq_sessao", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq_sessao")
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "token", nullable = false, unique = true)
    private String authToken;

    @ManyToOne(targetEntity = AplicacaoClienteVO.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "app_cliente_id", nullable = false)
    private AplicacaoClienteVO appCliente;

    @Column(name = "cliente_ip", nullable = false)
    private String ipCliente;

    @Column(name = "hostname", nullable = false)
    private String hostname;

    @Column(name = "porta", nullable = false)
    private String porta;

    @Column(name = "data_login", nullable = false)
    private Calendar dataHoraLogin;

    @Column(name = "data_logout", nullable = true)
    private Calendar dataHoraLogout;

    // Getters e setters omitidos

DAOs are interfaces that extend the Spring Data JpaRepository. So I do not have concrete DAOs implemented by me. I use Spring Data only. Below are the declarations of the methods that make the removals.

@Query("delete from SessaoVO sessao where sessao.appCliente = ?1")
void removeSessoesByCliente(AplicacaoClienteVO appCliente);

@Query("delete LogAcessoVO log where log.appCliente = ?1")
void removeLogsByApoCliente(AplicacaoClienteVO appCliente);

//Para remover a aplicação é usado o método default delete() do Spring Data.

Below is where the rule is applied and where the removal methods are called. Remembering that the error always occurs when committing the transaction.


@Transactional(propagation = Propagation.REQUIRED, readOnly = false)
public void remover(AplicacaoClienteVO appCliente) throws ServiceException {
    if (appCliente != null && appCliente.getId() != null) {

The error constraint (UK_9F63GD4XERQ7X8MNPKSKY69V) refers to the session table.

Information in the user_constraint ( select * from user_constraints where constraint_name = 'UK_9F63GD4XERQ7X8MNPKSKY69V' ) table:

    <LAST_CHANGE>05/06/2014 16:53:04</LAST_CHANGE>
asked by anonymous 06.06.2014 / 00:39

1 answer


Problem solved. Like I said, the question was a dumb mistake of mine. There was one more table that I should remove from a relationship I did not know. But I still do not understand why Oracle dropped the UK error.

06.06.2014 / 20:21