| 1 | -- Modify tables that are refered to. |
| 2 | ALTER TABLE newsletter MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; |
| 3 | ALTER TABLE payments MODIFY COLUMN id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT; |
| 4 | |
| 5 | -- Following tables all needs a sligth change in their definition to match their foreign correspondance. |
| 6 | ALTER TABLE group_events MODIFY COLUMN asso_id SMALLINT(5) UNSIGNED DEFAULT NULL; |
| 7 | ALTER TABLE newsletter_art MODIFY COLUMN id INT(11) UNSIGNED DEFAULT NULL; |
| 8 | ALTER TABLE newsletter_art MODIFY COLUMN cid TINYINT(3) UNSIGNED DEFAULT NULL; |
| 9 | ALTER TABLE newsletter_ins MODIFY COLUMN last INT(11) UNSIGNED DEFAULT NULL; |
| 10 | ALTER TABLE groups MODIFY COLUMN dom SMALLINT(5) UNSIGNED DEFAULT NULL; |
| 11 | ALTER TABLE payments MODIFY COLUMN asso_id SMALLINT(5) UNSIGNED DEFAULT NULL; |
| 12 | ALTER TABLE group_events MODIFY COLUMN paiement_id INT(11) UNSIGNED DEFAULT NULL; |
| 13 | ALTER TABLE group_event_participants MODIFY COLUMN item_id INT(11) UNSIGNED DEFAULT NULL; |
| 14 | |
| 15 | UPDATE groups SET dom = NULL WHERE dom = 0; |
| 16 | UPDATE payments SET asso_id = NULL WHERE asso_id = 0; |
| 17 | UPDATE newsletter_art SET cid = NULL WHERE cid = 0; |
| 18 | UPDATE newsletter_ins SET last = NULL WHERE last = 0; |
| 19 | |
| 20 | -- Deletes things that should have been deleted ealier. |
| 21 | DELETE FROM newsletter_art WHERE NOT EXISTS (SELECT * FROM newsletter WHERE newsletter.id = newsletter_art.id); |
| 22 | DELETE FROM log_last_sessions WHERE NOT EXISTS (SELECT * FROM log_sessions WHERE log_sessions.id = log_last_sessions.id); |
| 23 | DELETE FROM group_event_items WHERE NOT EXISTS (SELECT * FROM group_events WHERE group_events.eid = group_event_items.eid); |
| 24 | DELETE FROM group_announces_photo WHERE NOT EXISTS (SELECT * FROM group_announces WHERE group_announces.id = group_announces_photo.eid); |
| 25 | DELETE FROM group_announces_read WHERE NOT EXISTS (SELECT * FROM group_announces WHERE group_announces.id = group_announces_read.announce_id); |
| 26 | DELETE FROM group_event_participants WHERE NOT EXISTS (SELECT * FROM group_event_items WHERE group_event_items.eid = group_event_participants.eid AND group_event_items.item_id = group_event_participants.item_id); |
| 27 | DELETE FROM group_member_sub_requests WHERE NOT EXISTS (SELECT * FROM groups WHERE groups.id = group_member_sub_requests.asso_id); |
| 28 | DELETE FROM group_members WHERE NOT EXISTS (SELECT * FROM groups WHERE groups.id = group_members.asso_id); |
| 29 | |
| 30 | |
| 31 | -- Finaly we add the foreign keys. |
| 32 | ALTER TABLE accounts ADD FOREIGN KEY (type) REFERENCES account_types (type) ON DELETE CASCADE ON UPDATE CASCADE; |
| 33 | ALTER TABLE accounts ADD FOREIGN KEY (skin) REFERENCES skins (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 34 | ALTER TABLE announce_photos ADD FOREIGN KEY (eid) REFERENCES announces (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 35 | ALTER TABLE announce_read ADD FOREIGN KEY (evt_id) REFERENCES announces (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 36 | ALTER TABLE forum_subs ADD FOREIGN KEY (fid) REFERENCES forums (fid) ON DELETE CASCADE ON UPDATE CASCADE; |
| 37 | ALTER TABLE geoloc_administrativeareas ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 38 | ALTER TABLE geoloc_countries ADD FOREIGN KEY (belongsTo) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 39 | ALTER TABLE geoloc_localities ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 40 | ALTER TABLE geoloc_subadministrativeareas ADD FOREIGN KEY (country) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE; |
| 41 | ALTER TABLE group_announces ADD FOREIGN KEY (asso_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 42 | ALTER TABLE group_announces_photo ADD FOREIGN KEY (eid) REFERENCES group_announces (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 43 | ALTER TABLE group_announces_read ADD FOREIGN KEY (announce_id) REFERENCES group_announces (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 44 | ALTER TABLE group_event_items ADD FOREIGN KEY (eid) REFERENCES group_events (eid) ON DELETE CASCADE ON UPDATE CASCADE; |
| 45 | ALTER TABLE group_event_participants ADD FOREIGN KEY (eid, item_id) REFERENCES group_event_items (eid, item_id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 46 | ALTER TABLE group_events ADD FOREIGN KEY (asso_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 47 | ALTER TABLE group_events ADD FOREIGN KEY (paiement_id) REFERENCES payments (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 48 | ALTER TABLE group_member_sub_requests ADD FOREIGN KEY (asso_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 49 | ALTER TABLE group_members ADD FOREIGN KEY (asso_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 50 | ALTER TABLE groups ADD FOREIGN KEY (dom) REFERENCES group_dom (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 51 | ALTER TABLE log_events ADD FOREIGN KEY (session) REFERENCES log_sessions (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 52 | ALTER TABLE log_events ADD FOREIGN KEY (action) REFERENCES log_actions (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 53 | ALTER TABLE log_last_sessions ADD FOREIGN KEY (id) REFERENCES log_sessions (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 54 | ALTER TABLE newsletter_art ADD FOREIGN KEY (id) REFERENCES newsletter (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 55 | ALTER TABLE newsletter_art ADD FOREIGN KEY (cid) REFERENCES newsletter_cat (cid) ON DELETE CASCADE ON UPDATE CASCADE; |
| 56 | ALTER TABLE newsletter_ins ADD FOREIGN KEY (last) REFERENCES newsletter (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 57 | ALTER TABLE payments ADD FOREIGN KEY (asso_id) REFERENCES groups (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 58 | ALTER TABLE reminder ADD FOREIGN KEY (type_id) REFERENCES reminder_type (type_id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 59 | ALTER TABLE survey_answers ADD FOREIGN KEY (vote_id) REFERENCES survey_votes (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 60 | ALTER TABLE survey_votes ADD FOREIGN KEY (survey_id) REFERENCES surveys (id) ON DELETE CASCADE ON UPDATE CASCADE; |
| 61 | |
| 62 | -- vim:set syntax=mysql: |