ALTER TABLE affiliate_links     ADD CONSTRAINT affiliate_links_rid_fkey      FOREIGN KEY (rid)       REFERENCES releases      (id);
ALTER TABLE changes             ADD CONSTRAINT changes_requester_fkey        FOREIGN KEY (requester) REFERENCES users         (id) ON DELETE SET DEFAULT;
ALTER TABLE chars               ADD CONSTRAINT chars_latest_fkey             FOREIGN KEY (latest)    REFERENCES chars_rev     (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE chars_rev           ADD CONSTRAINT chars_rev_id_fkey             FOREIGN KEY (id)        REFERENCES changes       (id);
ALTER TABLE chars_rev           ADD CONSTRAINT chars_rev_cid_fkey            FOREIGN KEY (cid)       REFERENCES chars         (id);
ALTER TABLE chars_rev           ADD CONSTRAINT chars_rev_main_fkey           FOREIGN KEY (main)      REFERENCES chars         (id);
ALTER TABLE chars_traits        ADD CONSTRAINT chars_traits_cid_fkey         FOREIGN KEY (cid)       REFERENCES chars_rev     (id);
ALTER TABLE chars_traits        ADD CONSTRAINT chars_traits_tid_fkey         FOREIGN KEY (tid)       REFERENCES traits        (id);
ALTER TABLE chars_vns           ADD CONSTRAINT chars_vns_cid_fkey            FOREIGN KEY (cid)       REFERENCES chars_rev     (id);
ALTER TABLE chars_vns           ADD CONSTRAINT chars_vns_vid_fkey            FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE chars_vns           ADD CONSTRAINT chars_vns_rid_fkey            FOREIGN KEY (rid)       REFERENCES releases      (id);
ALTER TABLE notifications       ADD CONSTRAINT notifications_uid_fkey        FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE notifications       ADD CONSTRAINT notifications_c_byuser_fkey   FOREIGN KEY (c_byuser)  REFERENCES users         (id) ON DELETE SET DEFAULT;
ALTER TABLE producers           ADD CONSTRAINT producers_latest_fkey         FOREIGN KEY (latest)    REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers           ADD CONSTRAINT producers_rgraph_fkey         FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid1_fkey FOREIGN KEY (pid1)      REFERENCES producers_rev (id);
ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid2_fkey FOREIGN KEY (pid2)      REFERENCES producers     (id);
ALTER TABLE producers_rev       ADD CONSTRAINT producers_rev_id_fkey         FOREIGN KEY (id)        REFERENCES changes       (id);
ALTER TABLE producers_rev       ADD CONSTRAINT producers_rev_pid_fkey        FOREIGN KEY (pid)       REFERENCES producers     (id);
ALTER TABLE quotes              ADD CONSTRAINT quotes_vid_fkey               FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE releases            ADD CONSTRAINT releases_latest_fkey          FOREIGN KEY (latest)    REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_lang       ADD CONSTRAINT releases_lang_rid_fkey        FOREIGN KEY (rid)       REFERENCES releases_rev  (id);
ALTER TABLE releases_media      ADD CONSTRAINT releases_media_rid_fkey       FOREIGN KEY (rid)       REFERENCES releases_rev  (id);
ALTER TABLE releases_platforms  ADD CONSTRAINT releases_platforms_rid_fkey   FOREIGN KEY (rid)       REFERENCES releases_rev  (id);
ALTER TABLE releases_producers  ADD CONSTRAINT releases_producers_rid_fkey   FOREIGN KEY (rid)       REFERENCES releases_rev  (id);
ALTER TABLE releases_producers  ADD CONSTRAINT releases_producers_pid_fkey   FOREIGN KEY (pid)       REFERENCES producers     (id);
ALTER TABLE releases_rev        ADD CONSTRAINT releases_rev_id_fkey          FOREIGN KEY (id)        REFERENCES changes       (id);
ALTER TABLE releases_rev        ADD CONSTRAINT releases_rev_rid_fkey         FOREIGN KEY (rid)       REFERENCES releases      (id);
ALTER TABLE releases_vn         ADD CONSTRAINT releases_vn_rid_fkey          FOREIGN KEY (rid)       REFERENCES releases_rev  (id);
ALTER TABLE releases_vn         ADD CONSTRAINT releases_vn_vid_fkey          FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE rlists              ADD CONSTRAINT rlists_uid_fkey               FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE rlists              ADD CONSTRAINT rlists_rid_fkey               FOREIGN KEY (rid)       REFERENCES releases      (id);
ALTER TABLE sessions            ADD CONSTRAINT sessions_uid_fkey             FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE staff               ADD CONSTRAINT staff_latest_fkey             FOREIGN KEY (latest)    REFERENCES staff_rev     (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE staff_alias         ADD CONSTRAINT staff_alias_rid_fkey          FOREIGN KEY (rid)       REFERENCES staff_rev     (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE staff_rev           ADD CONSTRAINT staff_rev_id_fkey             FOREIGN KEY (id)        REFERENCES changes       (id);
ALTER TABLE staff_rev           ADD CONSTRAINT staff_rev_sid_fkey            FOREIGN KEY (sid)       REFERENCES staff         (id);
ALTER TABLE staff_rev           ADD CONSTRAINT staff_rev_aid_fkey            FOREIGN KEY (aid,id)    REFERENCES staff_alias   (id,rid);
ALTER TABLE tags                ADD CONSTRAINT tags_addedby_fkey             FOREIGN KEY (addedby)   REFERENCES users         (id) ON DELETE SET DEFAULT;
ALTER TABLE tags_aliases        ADD CONSTRAINT tags_aliases_tag_fkey         FOREIGN KEY (tag)       REFERENCES tags          (id);
ALTER TABLE tags_parents        ADD CONSTRAINT tags_parents_tag_fkey         FOREIGN KEY (tag)       REFERENCES tags          (id);
ALTER TABLE tags_parents        ADD CONSTRAINT tags_parents_parent_fkey      FOREIGN KEY (parent)    REFERENCES tags          (id);
ALTER TABLE tags_vn             ADD CONSTRAINT tags_vn_tag_fkey              FOREIGN KEY (tag)       REFERENCES tags          (id);
ALTER TABLE tags_vn             ADD CONSTRAINT tags_vn_vid_fkey              FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE tags_vn             ADD CONSTRAINT tags_vn_uid_fkey              FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE threads             ADD CONSTRAINT threads_id_fkey               FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE threads_posts       ADD CONSTRAINT threads_posts_tid_fkey        FOREIGN KEY (tid)       REFERENCES threads       (id);
ALTER TABLE threads_posts       ADD CONSTRAINT threads_posts_uid_fkey        FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
ALTER TABLE threads_boards      ADD CONSTRAINT threads_boards_tid_fkey       FOREIGN KEY (tid)       REFERENCES threads       (id);
ALTER TABLE traits              ADD CONSTRAINT traits_addedby_fkey           FOREIGN KEY (addedby)   REFERENCES users         (id);
ALTER TABLE traits              ADD CONSTRAINT traits_group_fkey             FOREIGN KEY ("group")   REFERENCES traits        (id);
ALTER TABLE traits_parents      ADD CONSTRAINT traits_parents_trait_fkey     FOREIGN KEY (trait)     REFERENCES traits        (id);
ALTER TABLE traits_parents      ADD CONSTRAINT traits_parents_parent_fkey    FOREIGN KEY (parent)    REFERENCES traits        (id);
ALTER TABLE traits_chars        ADD CONSTRAINT traits_chars_cid_fkey         FOREIGN KEY (cid)       REFERENCES chars         (id);
ALTER TABLE traits_chars        ADD CONSTRAINT traits_chars_tid_fkey         FOREIGN KEY (tid)       REFERENCES traits        (id);
ALTER TABLE users_prefs         ADD CONSTRAINT users_prefs_uid_fkey          FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE vn                  ADD CONSTRAINT vn_latest_fkey                FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn                  ADD CONSTRAINT vn_rgraph_fkey                FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
ALTER TABLE vn_anime            ADD CONSTRAINT vn_anime_aid_fkey             FOREIGN KEY (aid)       REFERENCES anime         (id);
ALTER TABLE vn_anime            ADD CONSTRAINT vn_anime_vid_fkey             FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
ALTER TABLE vn_relations        ADD CONSTRAINT vn_relations_vid1_fkey        FOREIGN KEY (vid1)      REFERENCES vn_rev        (id);
ALTER TABLE vn_relations        ADD CONSTRAINT vn_relations_vid2_fkey        FOREIGN KEY (vid2)      REFERENCES vn            (id);
ALTER TABLE vn_rev              ADD CONSTRAINT vn_rev_id_fkey                FOREIGN KEY (id)        REFERENCES changes       (id);
ALTER TABLE vn_rev              ADD CONSTRAINT vn_rev_vid_fkey               FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE vn_screenshots      ADD CONSTRAINT vn_screenshots_vid_fkey       FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
ALTER TABLE vn_screenshots      ADD CONSTRAINT vn_screenshots_scr_fkey       FOREIGN KEY (scr)       REFERENCES screenshots   (id);
ALTER TABLE vn_screenshots      ADD CONSTRAINT vn_screenshots_rid_fkey       FOREIGN KEY (rid)       REFERENCES releases      (id);
ALTER TABLE vn_seiyuu           ADD CONSTRAINT vn_seiyuu_cid_fkey            FOREIGN KEY (cid)       REFERENCES chars         (id);
ALTER TABLE vn_seiyuu           ADD CONSTRAINT vn_seiyuu_vid_fkey            FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
ALTER TABLE vn_staff            ADD CONSTRAINT vn_staff_vid_fkey             FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
ALTER TABLE vnlists             ADD CONSTRAINT vnlists_uid_fkey              FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE vnlists             ADD CONSTRAINT vnlists_vid_fkey              FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE votes               ADD CONSTRAINT votes_uid_fkey                FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE votes               ADD CONSTRAINT votes_vid_fkey                FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE wlists              ADD CONSTRAINT wlists_uid_fkey               FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE wlists              ADD CONSTRAINT wlists_vid_fkey               FOREIGN KEY (vid)       REFERENCES vn            (id);


CREATE        INDEX affiliate_links_rid    ON affiliate_links (rid) WHERE NOT hidden;
CREATE        INDEX releases_producers_rid ON releases_producers (rid);
CREATE        INDEX releases_vn_vid        ON releases_vn (vid);
CREATE        INDEX tags_vn_date           ON tags_vn (date);
CREATE        INDEX tags_vn_vid            ON tags_vn (vid);
CREATE        INDEX threads_posts_ts       ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
CREATE        INDEX vn_staff_vid           ON vn_staff (vid);
CREATE        INDEX vn_staff_aid           ON vn_staff (aid);
CREATE UNIQUE INDEX chars_vns_pkey         ON chars_vns (cid, vid, COALESCE(rid, 0));