Bug 466367

The portal schema is missing a lot (if not most) of foreign keys, which has caused data corruption in the past. Analysis follows:

Key:
[+] Missing
[*] Existing

-----------

accountid_orgid_mapping
[+] accountId -> account.id
[+] orgId -> ?

accounts
[+] created_by_id -> ?
[+] modified_by_id -> ?
[+] gid -> group.gid
[+] canonical_id -> ?

announce_permissions
|*] aid -> announcements.aid
[+] gid -> groups.gid

annoucements
[+] userid -> user.uid

bugzilla_comments
[+] eid -> events.eid
[+] tid -> tickets.tid
[+] bugzilla_id -> bugzilla_ids.bugzilla_id
|*] userid -> users.uid

bugzilla_confidential_groups
[+] it_gid -> group.gid

bugzilla_ids
[+] tid -> tickets.tid

categories

contacts

customer_entitlement_cache
[+] user_cache_id -> customer_user_cache.user_id

customer_user_cache
[+] user_id_FK] users.uid

customers_revenue_quintiles

customers_strategic

download_audit
[+] repository_id -> repositories.id
[+] userid -> users.uid

event_modifications
[+] eid -> events.eid
[+] userid -> users.uid

events
[+] tid -> tickets.tid
[+] userid -> users.uid
[+] fid -> files.fid
[+] rid -> ?

file_permissions
[+] fid -> files.fid
[+] gid -> groups.gid

files
[+] tid -> tickets.tid

gid_revenue_quintiles
|*] gid -> groups.gid

gid_strategic
|*] gid -> groups.gid

group_categories
[+] gid -> groups.gid
[+] cid -> categories.cid

group_escalation_points
[+] gid -> groups.gid
[+] egid -> groups.gid

group_istatuses
[+] gid -> groups.gid
[+] isid -> istatuses.isid

group_product_assignment
[+] gid -> groups.gid
[+] pid -> products.pid

group_products
[+] gid -> groups.gid
[+] pid -> products.pid

group_resolutions
|*] gid -> groups.gid
|*] rid -> resolutions.rid

group_sla
[+] gid -> groups.gid

group_statuses
[+] gid -> groups.gid
[+] sid -> statuses.sid

group_users
[+] gid -> groups.gid
[+] userid -> users.uid

knowledge_dashboard

line_managers
[+] sgid -> sgroups.sgid
[+] userid -> users.uid

logs
[+] log_user -> users.uid

logtime
[+] tid -> tickets.tid
[+] userid -> users.uid

menus
[+] userid -> users.uid

notifications
[+] gid -> groups.gid
[+] userid -> users.uid

onsite_visits
[+] gid -> groups.gid
[+] uid -> users.uid

privs
[+] userid -> users.uid

repository

sessions

sgroups
|*] istatus_id -> istatuses.isid

sip_reviews
[+] gid -> groups.gid
[+] uid -> users.uid

snapshot_metadata

software
[+] pid -> products.pid
[+] vid -> versions.vid

status_reports
[+] userid -> users.uid
[+] gid -> groups.gid

subjective_scoring
[+] tid -> tickets.tid
[+] userid -> users.uid
[+] eid -> events.eid

subscriptions
[+] userid -> users.uid
[+] tid -> tickets.tid

ticket_comment
[+] ticket_id -> tickets.tid
[+] created_by -> users.uid

ticket_fields

ticket_grouping

ticket_groups
[+] tid -> tickets.tid
[+] gid -> groups.gid
[+] assigned_to -> groups.gid

ticket_history
[+] ticket_id -> tickets.tid
[+] field_id -> ticket_fields.id
[+] created_by -> users.uid

ticket_link_groups

ticket_metadata_snapshot
[+] ticket_id -> tickets.tid

ticket_requesters
[+] tid -> tickets.tid

tickets
[+] tid -> tickets.tid
[+] gid -> groups.gid
[+] opened_by -> users.uid
[+] assigned_to -> users.uid
[+] cid -> categories.cid
[+] pid -> products.pid
[+] istatus -> istatuses.isid
[+] status -> statuses.sid
[+] rid -> resolutions.rid
[+] product_version -> versions.vid
|*] product_module -> modules.mid
[+] org_id -> ?

tld_glob_group_map
[+] gid -> groups.gid
[+] tldpid -> tld_prefs.tldpid

tld_prefs

tld_user_group_map
[+] uid -> users.uid
[+] gid -> groups.gid
[+] tldpid -> tld_prefs.tldpid

tld_user_myassign_map
[+] uid -> users.uid
[+] tldpid -> tld_prefs.tldpid

tld_user_myopen_map
[+] uid -> users.uid
[+] tldpid -> tld_prefs.tldpid

tld_user_myrecent_map
[+] uid -> users.uid
[+] tldpid -> tld_prefs.tldpid

tld_user_mysubscribe_map
[+] uid -> users.uid
[+] tldpid -> tld_prefs.tldpid

transaction_events
[+] tid -> tickets.tid
[+] eid -> events.eid
[+] userid -> users.uid

user_entitlements
[+] userid -> users.uid
[+] entitlement_id -> entitlements.id

users
|*] preferred_gid -> groups.gid

versions
[+] pid -> products.pid

versions_modules
[+] vid -> versions.vid
[+] mid -> modules.mid

view_tracking
[+] vid -> versions.vid
[+] tid -> tickets.tid
[+] userid -> users.uid |