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 enforce unique PPID per CP.
...
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; |
...