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