SQL Server fails to insert issue records

[gunterze]

2017-05-24 10:09:28,974 INFO  [org.dcm4chee.arc.store.impl.StoreServiceImpl] (EE-ManagedExecutorService-
default-Thread-105) DEVDCM4CHEE<-KHTAA-D(664): Failed to update DB - retry:
: javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.ConstraintViolationException: cou
ld not execute statement
:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint 'UK_t1p7jajas0mu12sx8jvtp2y0f'. Cannot insert duplicate key in object 'dbo.issuer'. The duplicate key value is (<NULL>, <NULL>).
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java

caused by https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values)

[petrkalina]

For sqlserver, this should do the trick:

alter table issuer drop constraint UK_gknfxd1vh283cmbg8ymia9ms8;
create unique index UK_gknfxd1vh283cmbg8ymia9ms8 on issuer(entity_id) where entity_id is not null;
alter table issuer drop constraint UK_t1p7jajas0mu12sx8jvtp2y0f;
create unique index UK_t1p7jajas0mu12sx8jvtp2y0f on issuer(entity_uid, entity_uid_type) where entity_id is not null and entity_uid_type is not null;
alter table patient_id drop constraint UK_31gvi9falc03xs94m8l3pgoid;
create unique index UK_31gvi9falc03xs94m8l3pgoid on patient_id(pat_id, issuer_fk) where pat_id is not null;

[petrkalina]

for oracle, according to this post: https://stackoverflow.com/questions/40027068/can-a-unique-constraint-column-have-2-or-more-null-values-oracle the multiple-null values in unique constraint should work correctly.

[petrkalina]

It might be worthwhile to update the sqlserver create script to include the above unique index definitions rather than the original unique constraint definitions to enable sqlserver based archive to work out of the box without any patch.

[gunterze]

I will take care for patching the create script for sqlserver.