Unique key violated when I’m doing a delete. Why?

Question

I have a problem that seems silly but I’m skating and I can’t solve it. I have a nice application made in Java using JPA 2 with Spring, Spring Data, JSF, etc... It works great. The basis 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 is already logged in. Logically, I delete all sessions of that application before deleting the application, otherwise it would occur FK Violation. But what happens here is that even I delete all sessions referring to that application and then trying to delete the application occurs a Unique key Violation!

Why would this key Unique occur in delete? It would make sense for this problem to occur when I enter a new record with duplicate username. But this is 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 will commit the transaction and not right after sending the delete.

EDITION: I’m adding the requested codes in the comments, a few other things that might help in the response.

Applicaocclusive

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

    private static final long serialVersionUID = 3401460653428856555L;

    @Id
    @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

Sessaovo

@Entity
@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;

    @Id
    @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)
    @Temporal(TemporalType.TIMESTAMP)
    private Calendar dataHoraLogin;

    @Column(name = "data_logout", nullable = true)
    @Temporal(TemporalType.TIMESTAMP)
    private Calendar dataHoraLogout;

    // Getters e setters omitidos

Daos are interfaces that extend the Jparepository of Spring Data. So I don’t have concrete Daos implemented by me. I only use Spring Data. Below are the statements of methods that make the removals.

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

@Modifying
@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. Remember that the error always occurs in the transaction commit.

Applicaoservice

@Transactional(propagation = Propagation.REQUIRED, readOnly = false)
public void remover(AplicacaoClienteVO appCliente) throws ServiceException {
    if (appCliente != null && appCliente.getId() != null) {
        this.sessaoDAO.delete(this.sessaoDAO.findByAppCliente(appCliente));
        this.logService.removerLogsPorAplicacao(appCliente);
        this.appClienteDAO.delete(appCliente);
    }
}

Error Constraint (UK_9F63GD4XERQ7X8MNPKSKY69V) refers to table of session.

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

<ROW>
    <OWNER>LOADERS_SCH</OWNER>
    <CONSTRAINT_NAME>UK_9F63GD4XERQ7X8MNPKSKY69V</CONSTRAINT_NAME>
    <CONSTRAINT_TYPE>U</CONSTRAINT_TYPE>
    <TABLE_NAME>WS_APLICACOES_CLIENTES</TABLE_NAME>
    <SEARCH_CONDITION></SEARCH_CONDITION>
    <R_OWNER></R_OWNER>
    <R_CONSTRAINT_NAME></R_CONSTRAINT_NAME>
    <DELETE_RULE></DELETE_RULE>
    <STATUS>ENABLED</STATUS>
    <DEFERRABLE>NOT DEFERRABLE</DEFERRABLE>
    <DEFERRED>IMMEDIATE</DEFERRED>
    <VALIDATED>VALIDATED</VALIDATED>
    <GENERATED>USER NAME</GENERATED>
    <BAD></BAD>
    <RELY></RELY>
    <LAST_CHANGE>05/06/2014 16:53:04</LAST_CHANGE>
    <INDEX_OWNER>LOADERS_SCH</INDEX_OWNER>
    <INDEX_NAME>UK_9F63GD4XERQ7X8MNPKSKY69V</INDEX_NAME>
    <INVALID></INVALID>
    <VIEW_RELATED></VIEW_RELATED>
</ROW>

Answer 1

Problem solved. As I said, in question, it was a stupid mistake of mine. There was one more table that I should remove a relation that I did not know. But I still don’t understand why Oracle was dropping the UK error.