pfts: make name a key

[gsrohde]

• ~~consolidate rows with equal names~~
• [x] Add NOT NULL and uniqueness constraints
• ~~Constrain name to contain no whitespace~~

Duplicate {name, modeltype_id}

ebi_production=# SELECT ARRAY_AGG(id), name FROM pfts GROUP BY name, modeltype_id HAVING COUNT(*) > 1;

shows that there are no violations of this constraint

Key constraints

Run

ALTER TABLE pfts ALTER COLUMN name SET NOT NULL;
ALTER TABLE pfts ADD CONSTRAINT unique_name_per_model UNIQUE (name, modeltype_id);

Value constraint

Is there any reason to allow whitespace in names? If not:

ALTER TABLE pfts ADD CONSTRAINT no_whitespace_in_name CHECK(name !~ '\s');

[robkooper]

name of pft does not have to be unique, we can have a populus per model, i think a good constraint would be (name, modetype_id)

[dlebauer]

Good idea @robkooper - there are no duplicates when name, modeltype_id are used

@gsrohde i've updated the original issue to reflect this change - should be ready to implement.

[robkooper]

white spaces can be in the name.

[gsrohde]

The same constraint has been added twice in two different migrations:

ADD CONSTRAINT unique_names_per_modeltype UNIQUE(name, modeltype_id)

ADD CONSTRAINT unique_name_per_model UNIQUE (name, modeltype_id)

One of these (the latter) should be removed.

[gsrohde]

Still need to deal with duplicate constraint mentioned in previous comment.