Error: Migration failed for change set db/3.4/schema.xml::Unique PPID per collection protocol::vlonushte: Reason: liquibase.exception.DatabaseException: java.sql.SQLIntegrityConstraintViolationException: ORA-02299: cannot validate (OPENSPECIMEN.CAT_CPR_CP_ID_PPID_UQ) - duplicate keys found
Reason: caTissue db allowed duplicate PPIDs within a CP whereas OS DB has a constraint to avoid this.
Solution: Update the duplicate PPID additional IDs to make it unique
Query to check duplicate PPIDs:
select cp.short_title, cpr.PROTOCOL_PARTICIPANT_ID, count(cpr.PROTOCOL_PARTICIPANT_ID)
from
catissue_coll_prot_reg cpr join catissue_collection_protocol cp on cp.identifier = cpr. COLLECTION_PROTOCOL_ID
group by
cp.short_title, cpr.PROTOCOL_PARTICIPANT_ID
having count(cpr.PROTOCOL_PARTICIPANT_ID) > 1;
Query to update duplicate PPIDs within same collection protocol as <PPID>_<PARTICIPANT_ID>
update
catissue_coll_prot_reg
set protocol_participant_id= concat(concat(protocol_participant_id, '_'), participant_id)
where
protocol_participant_id in (
select
protocol_participant_id
from
(SELECT * FROM catissue_coll_prot_reg) AS register
group by
register.COLLECTION_PROTOCOL_ID, register.protocol_participant_id
having count(register.protocol_participant_id) > 1);
Query to update null PPIDs with <CP_ID>_<PARTICIPANT_ID> update
update
catissue_coll_prot_reg
set
PROTOCOL_PARTICIPANT_ID = concat(concat(COLLECTION_PROTOCOL_ID, '_'), PARTICIPANT_ID)
where
PROTOCOL_PARTICIPANT_ID is null;