Set up foreign keys within the database. There is a small chance I may have broken emails, unlocks, or chat. Will test :)

1 files changed, 52 insertions, 2 deletions

diff --git a/db updates.sql b/db updates.sql
index 9a20ade..26495b3 100644
--- a/db updates.sql
+++ b/db updates.sql
@@ -1,10 +1,60 @@
 -- db updates reset after patch on March 24th.
 
---Dialog to challenges:
+-- Dialog to challenges:
 ALTER TABLE  `challenges` ADD  `dialogStart` VARCHAR( 200 ) DEFAULT NULL ,
 ADD  `dialogFail` VARCHAR( 200 ) DEFAULT NULL ,
 ADD  `dialogSuccess` VARCHAR( 200 ) DEFAULT NULL ;
 
 -- Add index necessary for stats updates
 ALTER TABLE `bluePathery`.`solutions`
-ADD INDEX `mapIDMoves` ( `mapID` , `moves` ) 
\ No newline at end of file
+ADD INDEX `mapIDMoves` ( `mapID` , `moves` );
+
+-- Remove unnecessary table
+DROP TABLE `guestSolutions`;
+DROP TABLE `statistics`;
+
+-- ADD FOREIGN KEY CONSTRAINTS --
+-- Achievements
+DELETE FROM achievements WHERE userID NOT IN (SELECT ID from users);
+ALTER TABLE `achievements` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Challenges
+ALTER TABLE `challenges` ADD FOREIGN KEY ( `mapID` ) REFERENCES `challengeMaps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Challenge solutions
+DELETE FROM challengeSolutions WHERE userID NOT IN (SELECT ID from users);
+ALTER TABLE `challengeSolutions` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `challengeSolutions` ADD FOREIGN KEY ( `challengeID` ) REFERENCES `challenges` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Chat
+ALTER TABLE `chat` CHANGE `userID` `userID` INT( 11 ) NULL;
+UPDATE chat SET userID = NULL WHERE userID <= 0;
+ALTER TABLE `chat` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Email Queue
+ALTER TABLE `emailQueue` ADD FOREIGN KEY ( `emailID` ) REFERENCES `emails` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- Emails
+ALTER TABLE `emails` CHANGE `fromUserID` `fromUserID` INT( 11 ) NULL;
+UPDATE emails SET fromUserID = NULL WHERE fromUserID <= 0;
+ALTER TABLE `emails` ADD FOREIGN KEY ( `fromUserID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- MapOfTheDay
+DELETE FROM mapOfTheDay WHERE mapID NOT IN (SELECT ID FROM maps);
+ALTER TABLE `mapOfTheDay` ADD FOREIGN KEY ( `mapID` ) REFERENCES `maps` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- Solutions
+DELETE FROM `solutions` WHERE userID NOT IN (SELECT ID FROM users);
+ALTER TABLE `solutions` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `solutions` ADD FOREIGN KEY ( `mapID` ) REFERENCES `maps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- Unlocks
+ALTER TABLE `unlocks` ENGINE = InnoDB; -- Looks like this was forgotten?
+DELETE FROM unlocks WHERE userID NOT IN (SELECT ID FROM users);
+ALTER TABLE `unlocks` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- UserData
+ALTER TABLE `userData` ENGINE = InnoDB; -- Looks like this was forgotten?
+ALTER TABLE `userData` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- END ADD FOREIGN KEY CONSTRAINTS
\ No newline at end of file