debug-mysql-foreign-key-varchar.sql

SHOW INDEXES FROM `room`;

--ALTER TABLE `room` DROP INDEX room_code_restriction_idx;
--ALTER TABLE `room` DROP INDEX room_user_restriction_idx;

ALTER TABLE `room` DROP FOREIGN KEY room_code_restriction_foreign;
ALTER TABLE `room` DROP FOREIGN KEY room_user_restriction_foreign;

ALTER TABLE `room` DROP COLUMN `code_restriction`;
ALTER TABLE `room` DROP COLUMN `user_restriction`;

DROP TABLE `room_code_restriction_type`;
DROP TABLE `room_user_restriction_type`;

--
-- Collation
--

SHOW VARIABLES LIKE "%version%"; 

-- New MySQL
-- utf8mb4_0900_ai_ci, utf8mb4_0900_ai_cs
ALTER DATABASE `stagedb` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE `room` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `room` DEFAULT CHARACTER SET = utf8mb4;

ALTER TABLE `room_code_restriction_type` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `room_code_restriction_type` DEFAULT CHARACTER SET = utf8mb4;

ALTER TABLE `room_user_restriction_type` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `room_code_restriction_type` DEFAULT CHARACTER SET = utf8mb4;

-- Old MySQL
-- utf8mb4_unicode_ci, utf8mb4_unicode_cs
ALTER DATABASE `stagedb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `room` DEFAULT CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `room` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `room_code_restriction_type` DEFAULT CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `room_code_restriction_type` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `room_user_restriction_type` DEFAULT CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `room_user_restriction_type` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

--
-- Other
--
ALTER TABLE `room_code_restriction_type` MODIFY `name` VARCHAR(24) NOT NULL;

UPDATE `room` SET `code_restriction` = 'NONE' WHERE `code_restriction` = null;
ALTER IGNORE TABLE `room` MODIFY `code_restriction` VARCHAR(24) NOT NULL DEFAULT 'NONE';
-- Query OK, 0 rows affected, 978 warnings (1.346 sec)
-- Records: 0  Duplicates: 0  Warnings: 978

CREATE INDEX room_code_restriction_name_idx ON `room_code_restriction_type` (`name`);
CREATE INDEX room_user_restriction_name_idx ON `room_user_restriction_type` (`name`);
CREATE INDEX room_code_restriction_idx ON `room` (`code_restriction`);
CREATE INDEX room_user_restriction_idx ON `room` (`user_restriction`);

--ALTER TABLE `room` DROP INDEX room_code_restriction_idx;
--ALTER TABLE `room` DROP INDEX room_user_restriction_idx;
--ALTER TABLE `room_code_restriction_type` DROP INDEX room_code_restriction_type_name_unique;
--ALTER TABLE `room_user_restriction_type` DROP INDEX room_user_restriction_type_name_unique;

SHOW INDEXES FROM `room`;
SHOW INDEXES FROM `room_code_restriction_type`;
SHOW INDEXES FROM `room_user_restriction_type`;

SHOW FULL COLUMNS FROM room_code_restriction_type;
SHOW FULL COLUMNS FROM room_user_restriction_type;
SHOW FULL COLUMNS FROM room;

UPDATE `room` SET `code_restriction` = 'NONE' WHERE `code_restriction` = '';
UPDATE `room` SET `user_restriction` = 'NONE' WHERE `user_restriction` = '';

alter table `room`
    add constraint `room_code_restriction_foreign`
    foreign key (`code_restriction`)
    references `room_code_restriction_type` (`name`);
    
SHOW ENGINE INNODB STATUS;