Make sure no foreign key indices are missing

13176
Reporter: mdoering
Assignee: trobertson
Type: Improvement
Summary: Make sure no foreign key indices are missing
Priority: Major
Resolution: Fixed
Status: Closed
Created: 2013-05-15 13:50:54.4
Updated: 2013-12-16 17:50:39.998
Resolved: 2013-12-12 11:50:13.672
        
Description: it seems we do not have any foreign key indices on the new registry, apart from the many 2 many unique constraints which are all limited to is_primary=true

For dataset tags for example there are 2 indices:
  dataset_tag_pkey
  dataset_tag_tag_key_key

They both originate from this sql:
CREATE TABLE dataset_tag
(
  dataset_key uuid NOT NULL REFERENCES dataset(key),
  tag_key integer NOT NULL UNIQUE REFERENCES tag(key) ON DELETE CASCADE,
  PRIMARY KEY (dataset_key, tag_key)
);

So we have a primary key index on the combined key, and one for the unique constraint on tag_key, but none for the dataset_key.
Isn't that needed?]]>
    

Attachment Screen Shot 2013-12-12 at 11.48.46 AM.png

Author: kbraak@gbif.org
Comment: Screenshot, showing foreign key constraints on dataset_tag table.
Created: 2013-12-12 11:49:47.524
Updated: 2013-12-12 11:49:47.524

Author: kbraak@gbif.org
Created: 2013-12-12 11:50:13.703
Updated: 2013-12-12 11:50:13.703
        
Foreign key constraints now set in Registry2 database schema.

Closing issue.