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 DB allowed duplicate PPIDs within a CP whereas OS DB has a constraint to avoid this. enforce unique PPID per CP.
Solution: Update the duplicate PPID additional PPIDs by suffixing participant IDs to make it them unique
Query to check presence of duplicate PPIDs:
| Code Block |
|---|
select cp.short_title, cpr. |
...
protocol_ |
...
participant_ |
...
id, count(cpr. |
...
protocol_ |
...
participant_ |
...
id) |
...
from catissue_coll_prot_reg |
...
cpr inner 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 to <PPID>_<PARTICIPANT_ID>
...
| Code Block |
|---|
update catissue_coll_prot_reg |
...
set protocol_participant_id = concat(concat(protocol_participant_id, '_'), participant_id) |
...
where protocol_participant_id in |
...
( select t.protocol_participant_id |
...
from ( select protocol_participant_id from catissue_coll_prot_reg |
...
reg group by reg.collection_protocol_id, reg.protocol_participant_id |
...
having count( |
...
reg.protocol_participant_id) > 1 ) t ); |
Query
...
to
...
update
...
null
...
PPIDs
...
to <CP_ID>_<PARTICIPANT_ID>
...
| Code Block |
|---|
update |
...
catissue_coll_prot_reg |
...
set protocol_participant_id = concat(concat( |
...
collection_ |
...
protocol_ |
...
id, '_'), |
...
participant_ |
...
id) |
...
where protocol_participant_id is null; |