Stable ID Changes in the Ensembl Core Schema

Ensembl 65 brought a major change to our core data model; we decided to merge the stable id tables with their parent tables. The relationship between a stable id record and its parent record was 1:1 making these tables an unnecessary step of normalisation & increasing the number of joins the API and MySQL had to perform. If you are using the Perl API this change will be transparent. However if you use direct SQL then views have been provided to replicate the stable id tables and allow your SQL to remain compatible. These views will be removed in Ensembl release 67. To support the new schema queries should be performed against the parent table for example:

  -- Original SQL
  select g.seq_region_start, g.seq_region_end
  from gene g join gene_stable_id gsi using (gene_id)
  where gsi.stable_id = 'ENSG00000139618';

  -- Should now be
  select g.seq_region_start, g.seq_region_end
  from gene g where g.stable_id = 'ENSG00000139618';

If you have any other queries about the changes then please contact helpdesk or our dev mailing list.

Comments are closed.