yields value constraints: needed cleanup and decisions

[dlebauer]

@gsrohde commented on Tue Feb 17 2015

[These are very similar to those for traits (GH #248).]

date, dateloc, date_year, date_month, date_day

• Decide what constraints to use
• Clean up data if needed
• Add to migration

mean

• Decide if a maximum bound is appropriate
• If so, add constraint

n

• Decide if n can be 1; change 1's to NULL if not. Similarly for 0.
• Add CHECK (n >= 2) or CHECK (n >= 1) or CHECK (n >= 0)

stat, statname, n consistency

• Decide on consistency constraints
• Fix violations
• Add needed constraints

cultivar_id, specie_id consistency

• Fix cases where specie_id is NULL but cultivar_id is not (12 cases)
• Fix inconsistent species-cultivar references
• Figure out how to write a consistency constraint
• Add it to migration

checked

• Decide how to handle NULLs
• Add not null constraint

Other NULLs

• Clean up NULLs in key columns (or rethink whether they need to be non-null)

Details

date, dateloc, date_year, date_month, date_day

See discussion in GH #239 and in section 3.26 of https://www.overleaf.com/2086241dwjyrd#/5297403/.

mean

Current max is 205.9.

n

See discussion in GH #231.

n = 1 in only one row.

stat, statname, n consistency

See discussion in GH #231.

cultivar_id, specie_id consistency

This is discussed in the context of foreign-key constraints. See GH #175.

To see inconsistencies in human-readable form, use

SELECT y_sp.scientificname AS "species referred to by yields table", c_sp.scientificname AS "species matching cultivar", c.name FROM yields y JOIN cultivars c ON y.cultivar_id = c.id JOIN species y_sp ON y_sp.id = y.specie_id JOIN species c_sp ON c.specie_id = c_sp.id WHERE y.specie_id != c.specie_id;

checked

checked is NULL in 156 rows.