missing FK in study_users and investigation_study

[antgonza]

@josenavas noticed while reviewing https://github.com/biocore/qiita/pull/1068/files#r28648682 that we don't need to remove links to study_users and investigation_study table for a study to be deleted. This means that we do not have FK constrains on those tables.

[antgonza]

@josenavas, @adamrp and @squirrelo, I was trying to fix this issue but I'm really confused by this and this line. Any ideas?

[josenavas]

I was also really surprised when I saw this error. The FK exists on DBschema, and checking in the information_schema tables it looks they exist:

SELECT * FROM information_schema.table_constraints WHERE table_name='study_users';
 constraint_catalog | constraint_schema |        constraint_name         | table_catalog | table_schema | table_name  | constraint_type | is_deferrable | initially_deferred 
--------------------+-------------------+--------------------------------+---------------+--------------+-------------+-----------------+---------------+--------------------
 qiita_test         | qiita             | fk_study_users_user            | qiita_test    | qiita        | study_users | FOREIGN KEY     | NO            | NO
 qiita_test         | qiita             | fk_study_users_study           | qiita_test    | qiita        | study_users | FOREIGN KEY     | NO            | NO
 qiita_test         | qiita             | idx_study_users                | qiita_test    | qiita        | study_users | PRIMARY KEY     | NO            | NO
 qiita_test         | qiita             | 123368937_123369715_1_not_null | qiita_test    | qiita        | study_users | CHECK           | NO            | NO
 qiita_test         | qiita             | 123368937_123369715_2_not_null | qiita_test    | qiita        | study_users | CHECK           | NO            | NO
(5 rows)

SELECT * FROM information_schema.table_constraints WHERE table_name='investigation_study';
 constraint_catalog | constraint_schema |        constraint_name         | table_catalog | table_schema |     table_name      | constraint_type | is_deferrable | initially_deferred 
--------------------+-------------------+--------------------------------+---------------+--------------+---------------------+-----------------+---------------+--------------------
 qiita_test         | qiita             | fk_investigation_study_study   | qiita_test    | qiita        | investigation_study | FOREIGN KEY     | NO            | NO
 qiita_test         | qiita             | fk_investigation_study         | qiita_test    | qiita        | investigation_study | FOREIGN KEY     | NO            | NO
 qiita_test         | qiita             | idx_investigation_study        | qiita_test    | qiita        | investigation_study | PRIMARY KEY     | NO            | NO
 qiita_test         | qiita             | 123368937_123369829_1_not_null | qiita_test    | qiita        | investigation_study | CHECK           | NO            | NO
 qiita_test         | qiita             | 123368937_123369829_2_not_null | qiita_test    | qiita        | investigation_study | CHECK           | NO            | NO

I found the issue of the confusion. The tests that we have for the Study.delete function are deleting a study created on the test. What this means is that the study is not shared with any user (study_users is empty) and is not associated with any investigation (investigation_study is also empty). This made the test to pass although the code was wrong.

In order to avoid future problems, we should modify the current test by sharing the study with another user and adding it to an investigation. This can be done by modifying the test using these two lines:

study = Study.create(User('test@foo.bar'), title, [1], self.info, investigation=Investigation(1))
study.share(User('shared@foo.bar'))

[antgonza]

That makes sense, thanks!