CREATE TABLE `master_role` (
  `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `role` VARCHAR(50) CHARACTER SET latin1 NOT NULL COMMENT 'Role',
  `active` TINYINT NOT NULL COMMENT 'Active',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT = 'Master Role';

CREATE TABLE `user_role` (
  `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `email` VARCHAR(150) NOT NULL COMMENT 'Email',
  `role_id` INTEGER NOT NULL COMMENT 'Role ID',
  PRIMARY KEY (`id`),
  KEY `fk_user_role_idx` (`role_id`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `master_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT = 'User Role';

SET foreign_key_checks=0;  # 關(guān)閉外鍵檢查
DROP TABLE Course;
SET foreign_key_checks=1;  # 開(kāi)啟外鍵檢查

#user_role表刪除外鍵
ALTER TABLE user_role DROP FOREIGN KEY fk_user_role;
#刪表
DROP TABLE IF EXISTS master_role;

CREATE TABLE `user_role` (
  `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `email` VARCHAR(150) NOT NULL COMMENT 'Email',
  `role_id` INTEGER NOT NULL COMMENT 'Role ID',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT = 'User Role';