upgrade/1.0.1/07_ids_foreign_keys.sql

   1 -- Prepares table having uid or pid set to 0.
   2 UPDATE aliases SET uid = NULL WHERE uid = 0;
   3 UPDATE log_sessions SET uid = NULL WHERE uid = 0;
   4 UPDATE log_sessions SET suid = NULL WHERE suid = 0;
   5 UPDATE register_marketing SET sender = NULL WHERE sender = 0;
   6 UPDATE register_mstats SET sender = NULL WHERE sender = 0;
   7 UPDATE requests SET pid = NULL WHERE pid = 0;
   8 UPDATE profile_addresses SET pid = NULL WHERE pid = 0;
   9
  10 -- Deltes erroneous data.
  11 DELETE FROM homonyms WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = homonyms.homonyme_id);
  12 DELETE FROM register_marketing WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = register_marketing.uid);
  13 DELETE FROM watch_nonins WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = watch_nonins.ni_id);
  14 DELETE FROM log_last_sessions WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = log_last_sessions.uid);
  15 DELETE FROM forum_profiles WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = forum_profiles.uid);
  16 DELETE FROM watch WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = watch.uid);
  17 DELETE FROM group_event_participants WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = group_event_participants.uid);
  18 DELETE FROM group_members WHERE NOT EXISTS (SELECT * FROM accounts WHERE accounts.uid = group_members.uid);
  19
  20 -- Following tables all refer to accounts.uid.
  21 ALTER TABLE account_auth_openid ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  22 ALTER TABLE account_lost_passwords ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  23 ALTER TABLE account_profiles ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  24 ALTER TABLE aliases ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  25 ALTER TABLE announce_read ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  26 ALTER TABLE announces ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  27 ALTER TABLE axletter_ins ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  28 ALTER TABLE axletter_rights ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  29 ALTER TABLE carvas ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  30 ALTER TABLE contacts ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  31 ALTER TABLE email_list_moderate ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  32 ALTER TABLE email_options ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  33 ALTER TABLE email_send_save ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  34 ALTER TABLE email_watch ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  35 ALTER TABLE forum_innd ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  36 ALTER TABLE forum_profiles ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  37 ALTER TABLE forum_subs ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  38 ALTER TABLE group_announces ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  39 ALTER TABLE group_announces_read ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  40 ALTER TABLE group_events ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  41 ALTER TABLE group_event_participants ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  42 ALTER TABLE group_member_sub_requests ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  43 ALTER TABLE group_members ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  44 ALTER TABLE homonyms ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  45 ALTER TABLE ip_watch ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  46 ALTER TABLE log_last_sessions ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  47 ALTER TABLE log_sessions ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  48 ALTER TABLE newsletter_ins ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  49 ALTER TABLE payment_transactions ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  50 ALTER TABLE register_marketing ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  51 ALTER TABLE register_mstats ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  52 ALTER TABLE register_pending ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  53 ALTER TABLE register_subs ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  54 ALTER TABLE reminder ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  55 ALTER TABLE requests ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  56 ALTER TABLE requests_hidden ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  57 ALTER TABLE survey_votes ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  58 ALTER TABLE surveys ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  59 ALTER TABLE watch ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  60 ALTER TABLE watch_nonins ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  61 ALTER TABLE watch_promo ADD FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  62
  63 -- Following tables all refer to accounts.uid, but they use a different name.
  64 ALTER TABLE contacts ADD FOREIGN KEY (contact) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  65 ALTER TABLE email_list_moderate ADD FOREIGN KEY (mid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  66 ALTER TABLE gapps_accounts ADD FOREIGN KEY (l_userid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  67 ALTER TABLE gapps_queue ADD FOREIGN KEY (q_owner_id) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  68 ALTER TABLE gapps_queue ADD FOREIGN KEY (q_recipient_id) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  69 ALTER TABLE gapps_nicknames ADD FOREIGN KEY (l_userid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  70 ALTER TABLE homonyms ADD FOREIGN KEY (homonyme_id) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  71 ALTER TABLE log_sessions ADD FOREIGN KEY (suid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  72 ALTER TABLE register_marketing ADD FOREIGN KEY (sender) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  73 ALTER TABLE register_mstats ADD FOREIGN KEY (sender) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  74 ALTER TABLE watch_nonins ADD FOREIGN KEY (ni_id) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE;
  75
  76 -- Following tables all refer to profiles.pid.
  77 ALTER TABLE account_profiles ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  78 ALTER TABLE requests ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  79 ALTER TABLE search_name ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  80 ALTER TABLE watch_profile ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  81 ALTER TABLE profile_binets ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  82 ALTER TABLE profile_corps ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  83 ALTER TABLE profile_display ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  84 ALTER TABLE profile_education ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  85 ALTER TABLE profile_job ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  86 ALTER TABLE profile_langskills ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  87 ALTER TABLE profile_medals ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  88 ALTER TABLE profile_mentor ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  89 ALTER TABLE profile_mentor_country ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  90 ALTER TABLE profile_mentor_sector ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  91 ALTER TABLE profile_name ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  92 ALTER TABLE profile_networking ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  93 ALTER TABLE profile_phones ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  94 ALTER TABLE profile_photos ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  95 ALTER TABLE profile_skills ADD FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE;
  96
  97 -- vim:set syntax=mysql: