jpa - Foreign key data integrity violation on Oracle X 10g

Question

I have an integration test running against either a MySQL instance or an Oracle instance.

The test passes fine when building the Maven project against the MySQL instance.

Here is the table structure:

drop table if exists operator;
create table operator (
  id bigint(20) unsigned not null auto_increment,
  version int(10) unsigned not null,
  name varchar(50),
  unique key name (name),
  description varchar(255),
  operator_id varchar(50),
  unique key operator_id (operator_id),
  image varchar(255),
  url varchar(255),
  country_id bigint(20) unsigned not null,
  primary key (id),
  unique key id (id),
  key country_id (country_id),
  constraint operator_fk1 foreign key (country_id) references country (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But when the same test runs against the Oracle instance then it gives me the exception:

1. insert into operator (version, country_id, description, image, name, operator_id, url, id) values (0, 19, 'The SFR operator', 'sfr.jpg', 'SFR', NULL, 'sfr.fr', 10)
java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (NITROPROJECT.OPERATOR_FK1) violated - parent key not found

Here is the table structure:

create table operator (
  id number(10) not null,
  version number(10) not null,
  name varchar2(50),
  constraint operator_u1 unique (name),
  description varchar2(255),
  operator_id varchar2(50),
  constraint operator_u2 unique (operator_id),
  image varchar2(255),
  url varchar2(255),
  country_id number(10) not null,
  constraint operator_pk primary key (id),
  constraint operator_fk1 foreign key (country_id) references country (id)
);
create sequence sq_id_operator increment by 1 start with 1 nomaxvalue nocycle cache 10;
create or replace trigger tr_id_inc_operator 
before insert 
on operator
for each row
declare
begin
  if (:new.id is null)
  then
    select sq_id_operator.nextval into :new.id from dual;
  end if;
end;
/

create table country (
  id number(10) not null,
  version number(10) not null,
  code varchar2(4) not null,
  constraint country_u1 unique (code),
  name varchar2(50) not null,
  list_order number(10),
  constraint country_pk primary key (id)
);
create sequence sq_id_country increment by 1 start with 1 nomaxvalue nocycle cache 10;
create index country_i1 on country (list_order, name);
create or replace trigger tr_id_inc_country
before insert
on country
for each row
declare
begin
  select sq_id_country.nextval into :new.id from dual;
end;
/

The country is created with the following service:

@Modifying
@Transactional(rollbackFor = EntityAlreadyExistsException.class)
@Override
public Country add(Country country) {
    if (findByCode(country.getCode()) == null) {
        // Save the returned id into the entity
        country = countryRepository.saveAndFlush(country);
        return country;
    } else {
        throw new EntityAlreadyExistsException();
    }
}

I can see in the console log that the country is actually created and that its primary key id is returned:

2014-09-19 13:00:05,839 DEBUG  [sqlonly]  com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
1. insert into country (version, code, list_order, name, id) values (0, 'fr', 1, 'France', 19)

In fact, I also added a finder call to make sure the country could be retrieved after it being created:

countryFR = new Country();
countryFR.setCode("fr");
countryFR.setName("France");
countryFR.setListOrder(1);
Country country = countryService.findByCode(countryFR.getCode());
if (country == null) {
    countryFR = countryService.add(countryFR);
} else {
    countryFR = country;
}
Country myc = countryService.findById(countryFR.getId());
if (myc != null) {
    logger.debug("==============>> Found the country id: " + myc.getId());
}

And the console log does show the logger output:

2014-09-19 13:00:05,854 DEBUG  [BTSControllerTest] ==============>> Found the country id: 19

NOTE: The console log does NOT show any select statement corresponding to that findById call.

And then comes the attempt to insert an operator:

1. insert into operator (version, country_id, description, image, name, operator_id, url, id) values (0, 19, 'The SFR operator', 'sfr.jpg', 'SFR', NULL, 'sfr.fr', 10)

You can see that the country id is the same as the one for the inserted country.

To sum things up:

The above note about the absence of select statement makes me wonder if the country was really inserted or not.

The primary key id of 19 retrieved after inserting the country leads me to think the country was actually inserted.

So how come Oracle complains it cannot find it for the operator foreign key ?

I'm using JPA2 hibernate-jpa-2.1-api 1.0.0.Final and spring-data-jpa 1.6.2.RELEASE and hibernate 4.3.6.Final

Here are the connection properties:

jpaPropertiesMap.put("hibernate.dialect", databaseProperties.getHibernateDialect());
jpaPropertiesMap.put("hibernate.show_sql", "true");
jpaPropertiesMap.put("hibernate.format_sql", "true");
jpaPropertiesMap.put("hibernate.hbm2ddl.auto", databaseProperties.getHibernateHbm2ddlAuto());
jpaPropertiesMap.put("hibernate.transaction.factory_class", "org.hibernate.transaction.JDBCTransactionFactory");
jpaPropertiesMap.put("hibernate.ejb.naming_strategy", "org.hibernate.cfg.ImprovedNamingStrategy");
jpaPropertiesMap.put("hibernate.c3p0.min_size", "5");
jpaPropertiesMap.put("hibernate.c3p0.max_size", "20");
jpaPropertiesMap.put("hibernate.c3p0.timeout", "1000");
jpaPropertiesMap.put("hibernate.c3p0.max_statements", "50");

EDIT:

I added the following properties to the JPA setup:

jpaPropertiesMap.put("hibernate.connection.autocommit", "true");
jpaPropertiesMap.put("hibernate.cache.use_query_cache", "false");
jpaPropertiesMap.put("hibernate.cache.use_second_level_cache", "false");

But it didn't change anything in the issue.

Answer 1

I found the solution. My sequence trigger was missing an if statement. Now it has one as in: if (:new.id is null)

create or replace trigger tr_id_inc_country
before insert
on country
for each row
declare
begin
  if (:new.id is null)
    then
    select sq_id_country.nextval into :new.id from dual;
  end if;
end;
/

I suppose Hibernate was getting a sequence number for the insert, and then Oracle was getting another one at commit time. I'm just guessing here.