在Oracle X 10g上违反外键数据完整性 [英] Foreign key data integrity violation on Oracle X 10g
问题描述
我有针对MySQL实例或Oracle实例运行的集成测试.
I have an integration test running against either a MySQL instance or an Oracle instance.
针对MySQL实例构建Maven项目时，测试通过正常.
The test passes fine when building the Maven project against the MySQL instance.
这是表结构:
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;
但是当针对Oracle实例运行相同的测试时，它给了我一个例外:
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
这是表结构:
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();
}
}
我可以在控制台日志中看到该国家实际上是创建的，并且返回了它的主键ID:
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)
事实上，我还添加了一个finder调用，以确保在创建国家后可以对其进行检索:
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
注意:控制台日志不会显示任何与该findById调用相对应的select语句.
NOTE: The console log does NOT show any select statement corresponding to that findById call.
然后尝试插入运算符:
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)
您可以看到国家/地区ID与插入的国家/地区的ID相同.
You can see that the country id is the same as the one for the inserted country.
总结一下:
上面关于没有select语句的注释使我想知道是否确实插入了该国家/地区.
The above note about the absence of select statement makes me wonder if the country was really inserted or not.
插入国家/地区后检索到的主键ID 19使我认为实际上已插入国家/地区.
The primary key id of 19 retrieved after inserting the country leads me to think the country was actually inserted.
那么Oracle为何抱怨它找不到操作员外键?
So how come Oracle complains it cannot find it for the operator foreign key ?
我正在使用JPA2 hibernate-jpa-2.1-api 1.0.0.Final和spring-data-jpa 1.6.2.RELEASE和hibernate 4.3.6.Final
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
以下是连接属性:
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");
我在JPA设置中添加了以下属性:
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");
但这并没有改变问题.
推荐答案
我找到了解决方案.我的序列触发器缺少if语句.现在它有一个，如:if(:new.id为null)
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;
/
我想Hibernate正在获得插入的序列号，然后Oracle在提交时又获得了一个序列号.我只是在猜测.
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.
这篇关于在Oracle X 10g上违反外键数据完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
