Informix allows only one NULL value in columns with UNIQUE constraints. Starting from version 6.0, an error occurs because a unique constraint is also created for columns with a NULL value allowed. This is demonstrated by a unit test.
org.hibernate.orm.test.annotations.entity.BasicHibernateAnnotationsTest#testCascadedDeleteOfChildEntitiesBug2
...
create table Player (
id integer not null,
oneVoneTeam_id integer,
team_id integer,
name varchar(255),
primary key (id)
)
alter table Player
add constraint unique (oneVoneTeam_id) constraint UKjlciu1kftyt5ov97rkjj0r9sx
...
insert
into
Player
(name, oneVoneTeam_id, team_id, id)
values
(?, ?, ?, ?)
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (1:VARCHAR) <- [Shalrie Joseph]
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:35 - binding parameter (2:INTEGER) <- [null]
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (3:INTEGER) <- [1]
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (4:INTEGER) <- [1]
Hibernate:
insert
into
Player
(name, oneVoneTeam_id, team_id, id)
values
(?, ?, ?, ?)
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (1:VARCHAR) <- [Steve Ralston]
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:35 - binding parameter (2:INTEGER) <- [null]
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (3:INTEGER) <- [1]
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (4:INTEGER) <- [2]
"org.hibernate.exception.ConstraintViolationException: could not execute statement [Unique constraint (informix.ukjlciu1kftyt5ov97rkjj0r9sx) violated.] [insert into Player (name,oneVoneTeam_id,team_id,id) values (?,?,?,?)]" type="org.hibernate.exception.ConstraintViolationException">org.hibernate.exception.ConstraintViolationException: could not execute statement [Unique constraint (informix.ukjlciu1kftyt5ov97rkjj0r9sx) violated.] [insert into Player (name,oneVoneTeam_id,team_id,id) values (?,?,?,?)]
at app//org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:62)
at app//org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
at app//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
at app//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
at app//org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:134)
at app//org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:55)
at app//org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:55)
at app//org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.doStaticInserts(InsertCoordinatorStandard.java:194)
at app//org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.coordinateInsert(InsertCoordinatorStandard.java:132)
at app//org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.insert(InsertCoordinatorStandard.java:104)
at app//org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:110)
at app//org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632)
at app//org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499)
at app//org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:371)
at app//org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41)
at app//org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
at app//org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1425)
at app//org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:487)
at app//org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2324)
at app//org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1981)
at app//org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439)
at app//org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:169)
at app//org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:267)
at app//org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
at app//org.hibernate.orm.test.annotations.entity.BasicHibernateAnnotationsTest.testCascadedDeleteOfChildEntitiesBug2(BasicHibernateAnnotationsTest.java:363)
at java.base@17.0.8.1/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base@17.0.8.1/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base@17.0.8.1/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base@17.0.8.1/java.lang.reflect.Method.invoke(Method.java:568)
at app//org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at app//org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at app//org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at app//org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:45)
at app//org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at app//org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at app//org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at app//org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:299)
at app//org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:293)
at java.base@17.0.8.1/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base@17.0.8.1/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Unique constraint (informix.ukjlciu1kftyt5ov97rkjj0r9sx) violated.
at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:418)
at com.informix.util.IfxErrMsg.buildIsamException(IfxErrMsg.java:403)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3023)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3274)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2269)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2194)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:813)
at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:225)
at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:967)
at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:322)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194)
... 36 more
Caused by: java.sql.SQLException: ISAM error: duplicate value for a record with unique key.
at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:424)
at com.informix.util.IfxErrMsg.buildException(IfxErrMsg.java:399)
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3278)
... 43 more
Informix allows only one NULL value in columns with UNIQUE constraints. Starting from version 6.0, an error occurs because a unique constraint is also created for columns with a NULL value allowed. This is demonstrated by a unit test.
org.hibernate.orm.test.annotations.entity.BasicHibernateAnnotationsTest#testCascadedDeleteOfChildEntitiesBug2 ... create table Player ( id integer not null, oneVoneTeam_id integer, team_id integer, name varchar(255), primary key (id) ) alter table Player add constraint unique (oneVoneTeam_id) constraint UKjlciu1kftyt5ov97rkjj0r9sx ... insert into Player (name, oneVoneTeam_id, team_id, id) values (?, ?, ?, ?) [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (1:VARCHAR) <- [Shalrie Joseph] [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:35 - binding parameter (2:INTEGER) <- [null] [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (3:INTEGER) <- [1] [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (4:INTEGER) <- [1] Hibernate: insert into Player (name, oneVoneTeam_id, team_id, id) values (?, ?, ?, ?) [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (1:VARCHAR) <- [Steve Ralston] [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:35 - binding parameter (2:INTEGER) <- [null] [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (3:INTEGER) <- [1] [subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (4:INTEGER) <- [2] "org.hibernate.exception.ConstraintViolationException: could not execute statement [Unique constraint (informix.ukjlciu1kftyt5ov97rkjj0r9sx) violated.] [insert into Player (name,oneVoneTeam_id,team_id,id) values (?,?,?,?)]" type="org.hibernate.exception.ConstraintViolationException">org.hibernate.exception.ConstraintViolationException: could not execute statement [Unique constraint (informix.ukjlciu1kftyt5ov97rkjj0r9sx) violated.] [insert into Player (name,oneVoneTeam_id,team_id,id) values (?,?,?,?)] at app//org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:62) at app//org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58) at app//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at app//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197) at app//org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:134) at app//org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:55) at app//org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:55) at app//org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.doStaticInserts(InsertCoordinatorStandard.java:194) at app//org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.coordinateInsert(InsertCoordinatorStandard.java:132) at app//org.hibernate.persister.entity.mutation.InsertCoordinatorStandard.insert(InsertCoordinatorStandard.java:104) at app//org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:110) at app//org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632) at app//org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499) at app//org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:371) at app//org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41) at app//org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127) at app//org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1425) at app//org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:487) at app//org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2324) at app//org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1981) at app//org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439) at app//org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:169) at app//org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:267) at app//org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101) at app//org.hibernate.orm.test.annotations.entity.BasicHibernateAnnotationsTest.testCascadedDeleteOfChildEntitiesBug2(BasicHibernateAnnotationsTest.java:363) at java.base@17.0.8.1/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base@17.0.8.1/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base@17.0.8.1/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base@17.0.8.1/java.lang.reflect.Method.invoke(Method.java:568) at app//org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59) at app//org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at app//org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56) at app//org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:45) at app//org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at app//org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at app//org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at app//org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:299) at app//org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:293) at java.base@17.0.8.1/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base@17.0.8.1/java.lang.Thread.run(Thread.java:833) Caused by: java.sql.SQLIntegrityConstraintViolationException: Unique constraint (informix.ukjlciu1kftyt5ov97rkjj0r9sx) violated. at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:418) at com.informix.util.IfxErrMsg.buildIsamException(IfxErrMsg.java:403) at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3023) at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3274) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2269) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2194) at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:813) at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:225) at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:967) at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:322) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194) ... 36 more Caused by: java.sql.SQLException: ISAM error: duplicate value for a record with unique key. at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:424) at com.informix.util.IfxErrMsg.buildException(IfxErrMsg.java:399) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3278) ... 43 more