Foreign keys are not defined

[almasaeed2010]

organism_analysis table uses drupal schema to install the table which ignores the FK definition.

Possible solutions:

1. Use entity delete hook to remove records
2. Create the table using raw SQL

We'll also need an update function to remove deleted entities from the linker table.

[almasaeed2010]

Steps to fix this:

• Run an update function to clear organism_analysis from any missing records
• Write an ALTER query to add the foreign keys with on delete cascade for both organism_id and analysis_id

[almasaeed2010]

Query to find deleted analysis:

select * from chado.organism_analysis oa
 LEFT join chado.analysis a on a.analysis_id = oa.analysis_id 
 where a.analysis_id is null

[almasaeed2010]

The query to add a foreign key is not working on my local dev. I am not sure why it fails on db_query but succeeds when its run in psql directly:

db_query('ALTER TABLE chado.organism_analysis
   ADD CONSTRAINT organism_analysis_organism_id_fk
   FOREIGN KEY (organism_id)
   REFERENCES chado.organism(organism_id)
   ON DELETE CASCADE')->execute();

  db_query('ALTER TABLE chado.organism_analysis 
   ADD CONSTRAINT organism_analysis_analysis_id_fk
   FOREIGN KEY (analysis_id) 
   REFERENCES chado.analysis(analysis_id)
   ON DELETE CASCADE')->execute();

I tried adding chado_set_active('chado') but that didn't change the result. I also tried using chado_query instead but no luck there either.

[bradfordcondon]

shouldnt this be a core issue? If a schema defines foreign keys, tripal should hook into schema and add them>

[bradfordcondon]

@almasaeed2010 just touching base on this.... my understanding is adding foreign keys does nothing when adding custom tables...

whats your opinion on this being a tripal core issue?