search

humlab-sead / sead_change_control

Sane SEAD change control using Sqitch.
1 stars 0 forks source link

20231129_DDL_MISSING_UNIQUE_CONSTRAINTS #142

Closed roger-mahler closed 9 months ago

roger-mahler commented 11 months ago

Project: ./general Change request: 20231129_DDL_MISSING_UNIQUE_CONSTRAINTS

Add missing unique constraints to the SEAD database.

roger-mahler commented 11 months ago

The table tbl_sample_alt_refs has duplicate records for columns alt_ref, physical_sample_id and alt_ref_type_id. Current staging has more duplicates than found in a freshly created deployed release chain, so some of the problems might have been resolved in existing change request. There still exists 139 dublicated identified by this SQL:

select r.alt_ref, physical_sample_id, r.alt_ref_type_id, count(*)
from tbl_sample_alt_refs r
group by r.alt_ref, physical_sample_id, r.alt_ref_type_id
having count(*) > 1
Click to expand ```log NOTICE: deleted duplicate sample_alt_ref_id: 1794 NOTICE: deleted duplicate sample_alt_ref_id: 5043 NOTICE: deleted duplicate sample_alt_ref_id: 1735 NOTICE: deleted duplicate sample_alt_ref_id: 1221 NOTICE: deleted duplicate sample_alt_ref_id: 2535 NOTICE: deleted duplicate sample_alt_ref_id: 5048 NOTICE: deleted duplicate sample_alt_ref_id: 3676 NOTICE: deleted duplicate sample_alt_ref_id: 4715 NOTICE: deleted duplicate sample_alt_ref_id: 2964 NOTICE: deleted duplicate sample_alt_ref_id: 4645 NOTICE: deleted duplicate sample_alt_ref_id: 2398 NOTICE: deleted duplicate sample_alt_ref_id: 4210 NOTICE: deleted duplicate sample_alt_ref_id: 4694 NOTICE: deleted duplicate sample_alt_ref_id: 4283 NOTICE: deleted duplicate sample_alt_ref_id: 9245 NOTICE: deleted duplicate sample_alt_ref_id: 9465 NOTICE: deleted duplicate sample_alt_ref_id: 7945 NOTICE: deleted duplicate sample_alt_ref_id: 8228 NOTICE: deleted duplicate sample_alt_ref_id: 9630 NOTICE: deleted duplicate sample_alt_ref_id: 9068 NOTICE: deleted duplicate sample_alt_ref_id: 9461 NOTICE: deleted duplicate sample_alt_ref_id: 7835 NOTICE: deleted duplicate sample_alt_ref_id: 8461 NOTICE: deleted duplicate sample_alt_ref_id: 8473 NOTICE: deleted duplicate sample_alt_ref_id: 7733 NOTICE: deleted duplicate sample_alt_ref_id: 7405 NOTICE: deleted duplicate sample_alt_ref_id: 7156 NOTICE: deleted duplicate sample_alt_ref_id: 6458 NOTICE: deleted duplicate sample_alt_ref_id: 5741 NOTICE: deleted duplicate sample_alt_ref_id: 9417 NOTICE: deleted duplicate sample_alt_ref_id: 8320 NOTICE: deleted duplicate sample_alt_ref_id: 9472 NOTICE: deleted duplicate sample_alt_ref_id: 5710 NOTICE: deleted duplicate sample_alt_ref_id: 8599 NOTICE: deleted duplicate sample_alt_ref_id: 8148 NOTICE: deleted duplicate sample_alt_ref_id: 6787 NOTICE: deleted duplicate sample_alt_ref_id: 8468 NOTICE: deleted duplicate sample_alt_ref_id: 8348 NOTICE: deleted duplicate sample_alt_ref_id: 9373 NOTICE: deleted duplicate sample_alt_ref_id: 9741 NOTICE: deleted duplicate sample_alt_ref_id: 9459 NOTICE: deleted duplicate sample_alt_ref_id: 8990 NOTICE: deleted duplicate sample_alt_ref_id: 7995 NOTICE: deleted duplicate sample_alt_ref_id: 9106 NOTICE: deleted duplicate sample_alt_ref_id: 8055 NOTICE: deleted duplicate sample_alt_ref_id: 9420 NOTICE: deleted duplicate sample_alt_ref_id: 9632 NOTICE: deleted duplicate sample_alt_ref_id: 8501 NOTICE: deleted duplicate sample_alt_ref_id: 6996 NOTICE: deleted duplicate sample_alt_ref_id: 9088 NOTICE: deleted duplicate sample_alt_ref_id: 7137 NOTICE: deleted duplicate sample_alt_ref_id: 7961 NOTICE: deleted duplicate sample_alt_ref_id: 9507 NOTICE: deleted duplicate sample_alt_ref_id: 8983 NOTICE: deleted duplicate sample_alt_ref_id: 8610 NOTICE: deleted duplicate sample_alt_ref_id: 9268 NOTICE: deleted duplicate sample_alt_ref_id: 9562 NOTICE: deleted duplicate sample_alt_ref_id: 7863 NOTICE: deleted duplicate sample_alt_ref_id: 8947 NOTICE: deleted duplicate sample_alt_ref_id: 9307 NOTICE: deleted duplicate sample_alt_ref_id: 7356 NOTICE: deleted duplicate sample_alt_ref_id: 7695 NOTICE: deleted duplicate sample_alt_ref_id: 9181 NOTICE: deleted duplicate sample_alt_ref_id: 8709 NOTICE: deleted duplicate sample_alt_ref_id: 9302 NOTICE: deleted duplicate sample_alt_ref_id: 7052 NOTICE: deleted duplicate sample_alt_ref_id: 9564 NOTICE: deleted duplicate sample_alt_ref_id: 5925 NOTICE: deleted duplicate sample_alt_ref_id: 9625 NOTICE: deleted duplicate sample_alt_ref_id: 6709 NOTICE: deleted duplicate sample_alt_ref_id: 7060 NOTICE: deleted duplicate sample_alt_ref_id: 8701 NOTICE: deleted duplicate sample_alt_ref_id: 8031 NOTICE: deleted duplicate sample_alt_ref_id: 7876 NOTICE: deleted duplicate sample_alt_ref_id: 7619 NOTICE: deleted duplicate sample_alt_ref_id: 7722 NOTICE: deleted duplicate sample_alt_ref_id: 7823 NOTICE: deleted duplicate sample_alt_ref_id: 7867 NOTICE: deleted duplicate sample_alt_ref_id: 6578 NOTICE: deleted duplicate sample_alt_ref_id: 7678 NOTICE: deleted duplicate sample_alt_ref_id: 9719 NOTICE: deleted duplicate sample_alt_ref_id: 9335 NOTICE: deleted duplicate sample_alt_ref_id: 9112 NOTICE: deleted duplicate sample_alt_ref_id: 8878 NOTICE: deleted duplicate sample_alt_ref_id: 9477 NOTICE: deleted duplicate sample_alt_ref_id: 8087 NOTICE: deleted duplicate sample_alt_ref_id: 8090 NOTICE: deleted duplicate sample_alt_ref_id: 7689 NOTICE: deleted duplicate sample_alt_ref_id: 8231 NOTICE: deleted duplicate sample_alt_ref_id: 5770 NOTICE: deleted duplicate sample_alt_ref_id: 9458 NOTICE: deleted duplicate sample_alt_ref_id: 6979 NOTICE: deleted duplicate sample_alt_ref_id: 7651 NOTICE: deleted duplicate sample_alt_ref_id: 7316 NOTICE: deleted duplicate sample_alt_ref_id: 9772 NOTICE: deleted duplicate sample_alt_ref_id: 9536 NOTICE: deleted duplicate sample_alt_ref_id: 8502 NOTICE: deleted duplicate sample_alt_ref_id: 8861 NOTICE: deleted duplicate sample_alt_ref_id: 9446 NOTICE: deleted duplicate sample_alt_ref_id: 7391 NOTICE: deleted duplicate sample_alt_ref_id: 6848 NOTICE: deleted duplicate sample_alt_ref_id: 8414 NOTICE: deleted duplicate sample_alt_ref_id: 8691 NOTICE: deleted duplicate sample_alt_ref_id: 7380 NOTICE: deleted duplicate sample_alt_ref_id: 8257 NOTICE: deleted duplicate sample_alt_ref_id: 8525 NOTICE: deleted duplicate sample_alt_ref_id: 8802 NOTICE: deleted duplicate sample_alt_ref_id: 9214 NOTICE: deleted duplicate sample_alt_ref_id: 6272 NOTICE: deleted duplicate sample_alt_ref_id: 8359 NOTICE: deleted duplicate sample_alt_ref_id: 6554 NOTICE: deleted duplicate sample_alt_ref_id: 8591 NOTICE: deleted duplicate sample_alt_ref_id: 7685 NOTICE: deleted duplicate sample_alt_ref_id: 8317 NOTICE: deleted duplicate sample_alt_ref_id: 8753 NOTICE: deleted duplicate sample_alt_ref_id: 5932 NOTICE: deleted duplicate sample_alt_ref_id: 9225 NOTICE: deleted duplicate sample_alt_ref_id: 9247 NOTICE: deleted duplicate sample_alt_ref_id: 8290 NOTICE: deleted duplicate sample_alt_ref_id: 7940 NOTICE: deleted duplicate sample_alt_ref_id: 6091 NOTICE: deleted duplicate sample_alt_ref_id: 9215 NOTICE: deleted duplicate sample_alt_ref_id: 8446 NOTICE: deleted duplicate sample_alt_ref_id: 9565 NOTICE: deleted duplicate sample_alt_ref_id: 7311 NOTICE: deleted duplicate sample_alt_ref_id: 6366 NOTICE: deleted duplicate sample_alt_ref_id: 9104 NOTICE: deleted duplicate sample_alt_ref_id: 8564 NOTICE: deleted duplicate sample_alt_ref_id: 8494 NOTICE: deleted duplicate sample_alt_ref_id: 6673 NOTICE: deleted duplicate sample_alt_ref_id: 9171 NOTICE: deleted duplicate sample_alt_ref_id: 8374 NOTICE: deleted duplicate sample_alt_ref_id: 8965 NOTICE: deleted duplicate sample_alt_ref_id: 8854 NOTICE: deleted duplicate sample_alt_ref_id: 8101 NOTICE: deleted duplicate sample_alt_ref_id: 8008 NOTICE: deleted duplicate sample_alt_ref_id: 9414 NOTICE: deleted duplicate sample_alt_ref_id: 6882 NOTICE: deleted duplicate sample_alt_ref_id: 9357 ```

The following unique constrainet is added:

alter table tbl_sample_alt_refs
    add constraint uq_tbl_sample_alt_refs unique (physical_sample_id, alt_ref, alt_ref_type_id);
roger-mahler commented 11 months ago

The table tbl_site_references has a duplicate record for columns site_id and biblio_id. 

  select site_id, biblio_id, count(*)
  from tbl_site_references
  group by site_id, biblio_id
  having count(*) > 1
Click to expand ```log NOTICE: duplicate site_reference_id: 4431 ```

The following unique constrainet is added:

alter table tbl_site_references
    add constraint uq_site_references unique (site_id, biblio_id);