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;
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
);
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 profile_modifications
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
51 ALTER TABLE register_marketing
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
52 ALTER TABLE register_mstats
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
53 ALTER TABLE register_pending
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
54 ALTER TABLE register_subs
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
55 ALTER TABLE reminder
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
56 ALTER TABLE requests
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
57 ALTER TABLE requests_hidden
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
58 ALTER TABLE survey_votes
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
59 ALTER TABLE surveys
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
60 ALTER TABLE watch
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
61 ALTER TABLE watch_nonins
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
62 ALTER TABLE watch_promo
ADD FOREIGN KEY (uid
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
64 -- Following tables all refer to accounts.uid, but they use a different name.
65 ALTER TABLE contacts
ADD FOREIGN KEY (contact
) REFERENCES accounts (uid
) ON DELETE CASCADE ON UPDATE CASCADE;
66 ALTER TABLE email_list_moderate
ADD FOREIGN KEY (mid
) 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;
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_job_term
ADD FOREIGN KEY (pid
, jid
) REFERENCES profile_job (pid
, id) ON DELETE CASCADE ON UPDATE CASCADE,
88 ALTER TABLE profile_langskills
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
89 ALTER TABLE profile_medals
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
90 ALTER TABLE profile_mentor
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
91 ALTER TABLE profile_mentor_country
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
92 ALTER TABLE profile_mentor_sector
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
93 ALTER TABLE profile_mentor_term
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
94 ALTER TABLE profile_modifications
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
95 ALTER TABLE profile_name
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
96 ALTER TABLE profile_networking
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
97 ALTER TABLE profile_phones
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
98 ALTER TABLE profile_photos
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
99 ALTER TABLE profile_skills
ADD FOREIGN KEY (pid
) REFERENCES profiles (pid
) ON DELETE CASCADE ON UPDATE CASCADE;
101 -- vim:set syntax=mysql: