variables value constraints: needed cleanup and decisions

[gsrohde]

description

• [ ] Fix value that isn't whitespace-normalized
• [ ] Add constraint

units

• [ ] Decide if any constraints are possible besides whitespace-normalization and not null
• [ ] If so, add

min, max

• [ ] Decide whether to change these columns
• [ ] IFF statname is not null and n is not null:
  • restrict lower bound on mean to min - 2 * stat if statname in (HSD, LSD, SE)
  • restrict lower bound on mean to min - 2 * stat * sqrt(n) if stat = SD. In this case n = 1 if n is null

standard_name, standard_units, label, type

• [ ] Decide what to do with these columns

Details

description

The prevent_conflicting_range_changes trigger prevents updating certain rows that violate the range constraint even though we are only trying to update the description. There may be a way to make the trigger active only when the min or max column is being changed. Otherwise, we will have to fix range conflicts by widening the range or deleting some traits or temporarily deactivating the trigger.

units

See discussion of units in section 3.13 of https://www.overleaf.com/2086241dwjyrd#/5297403/.

min, max

For some inexplicable reason, these are varchars. A combined column of type numrange would be more appropriate. (See discussion in sectin 3.29 of https://www.overleaf.com/2086241dwjyrd#/5297403/.) If we leave them as varchars, they should be made non-NULL and be made to look like floating-point numbers, and a consistency check should be added to ensure min < max.

standard_name, standard_units, label, type

None of these columns is currently used.