yields: missing cultivars and sites

[gsrohde]

• [ ] Once the database is cleaned up, add these constraints
  • [x] Add cultivar foreign-key constraint
  • [ ] Add site foreign-key constraint

ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_cultivars_1" FOREIGN KEY ("cultivar_id") REFERENCES "cultivars" ("id") NOT VALID;
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id") NOT VALID;

• [x] MISSING CULTIVARS:

select cultivar_id, count(*) from yields where cultivar_id not in (select id from cultivars) or cultivar_id is null group by cultivar_id order by cultivar_id;
 cultivar_id | count 
-------------+-------
           0 |     1
         435 |    30
             |  2863
(3 rows)

• [ ] MISSING SITES:

select site_id, count(*) from yields where site_id not in (select id from sites) or site_id is null group by site_id order by site_id;
 site_id | count
---------+-------
      61 |     4
      81 |    12
      82 |    12
      86 |    24
      88 |    48
     248 |     1
     267 |     1
     514 |    26
     516 |    38
    2860 |    62
    2862 |    67
    2864 |    16
    2865 |     6
    2866 |     3
    2867 |     5
    2876 |    20
         |    28
(17 rows)

[dlebauer]

I've converted the orphaned cultivars to NULL. It will take a bit more work to sort out the orphaned sites.

update yields set cultivar_id = NULL where cultivar_id = 435
update yields set cultivar_id = NULL where cultivar_id = 0

Set site_id NULL where site_id = 0

update traits set site_id = NULL where site_id = 0;
update yields set site_id = NULL where site_id = 0;

[dlebauer]

More sites fixed:

## used traits table to identify site
update yields set site_id = 397, updated_at = now() where id in (7580, 7581, 7582);
update yields set site_id = 429, updated_at = now() where citation_id = 84;
update yields set site_id = 435, specie_id = 2955, updated_at = now() where citation_id = 91;


## found incorrect site assignment for citation
update traits set site_id = 126, updated_at = now() where citation_id = 67;
update yields set site_id = 126, updated_at = now() where citation_id = 67;

## based on traits table and reference
update yields set site_id = 463, cultivar_id = 102, treatment_id = 463 where citation_id = 65;

update traits set site_id = 158 where citation_id = 93;

update yields set site_id = 442 where citation_id = 98;

update yields set site_id = 126 where citation_id = 99;
update traits set site_id = 126 where citation_id = 99;

update yields set site_id = 126 where citation_id = 101;
update traits set site_id = 126 where citation_id = 101;

update yields set site_id = 451, updated_at = now() where citation_id = 105;
update yields set specie_id = 1072, cultivar_id = 268, updated_at = now() where id = 8492;
update yields set specie_id = 2955, cultivar_id = 269, updated_at = now() where id = 8493;