V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Lzjss
V2EX  ›  程序员

不懂就问,在 mysql 中关于 associative entity(Credentials)报错应该怎么改?

  •  
  •   Lzjss · 284 天前 · 206 次点击
    这是一个创建于 284 天前的主题,其中的信息可能已经有所发展或是发生改变。
    报错:Failed to add the foreign key constraint. Missing index for constraint 'FK_CREDENTIALS_USER' in the referenced table 'User'

    具体代码:
    DROP TABLE IF EXISTS `PharmacyManagementSystemDB`.`User` ;

    CREATE TABLE IF NOT EXISTS `PharmacyManagementSystemDB`.`User` (
    `user_id` TINYINT(9) NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(45) NOT NULL,
    `first_name` VARCHAR(45) NOT NULL,
    `last_name` VARCHAR(45) NOT NULL,
    `full_name` VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name," ",last_name)),
    `phone_num` INT NOT NULL,
    PRIMARY KEY (`user_id`, `email`))
    ENGINE = InnoDB;

    -- -----------------------------------------------------
    -- Table `PharmacyManagementSystemDB`.`Account`
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `PharmacyManagementSystemDB`.`Account` ;

    CREATE TABLE IF NOT EXISTS `PharmacyManagementSystemDB`.`Account` (
    `account_id` TINYINT NOT NULL AUTO_INCREMENT,
    `created` TIMESTAMP NOT NULL,
    `expires` TIMESTAMP NOT NULL,
    `user_id` TINYINT NOT NULL,
    `region` TINYINT NOT NULL,
    `role` TINYINT NOT NULL,
    PRIMARY KEY (`account_id`),
    INDEX `FK_PK_USER_ACCOUNT_idx` (`user_id` ASC) VISIBLE,
    INDEX `FK_PK_REGION_ACCOUNT_idx` (`region` ASC) VISIBLE,
    INDEX `FK_PK_ROLES_ACCOUNT_idx` (`role` ASC) VISIBLE,
    CONSTRAINT `FK_PK_USER_ACCOUNT`
    FOREIGN KEY (`user_id`)
    REFERENCES `PharmacyManagementSystemDB`.`User` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `FK_PK_REGION_ACCOUNT`
    FOREIGN KEY (`region`)
    REFERENCES `PharmacyManagementSystemDB`.`Region` (`region_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `FK_PK_ROLES_ACCOUNT`
    FOREIGN KEY (`role`)
    REFERENCES `PharmacyManagementSystemDB`.`Roles` (`Roles_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;

    -- -----------------------------------------------------
    -- Table `PharmacyManagementSystemDB`.`Credentials`
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `PharmacyManagementSystemDB`.`Credentials` ;

    CREATE TABLE IF NOT EXISTS `PharmacyManagementSystemDB`.`Credentials` (
    `credentials_id` TINYINT NOT NULL,
    `email` VARCHAR(45) NOT NULL,
    `passwork` VARCHAR(45) NOT NULL,
    `account_id` TINYINT NOT NULL,
    PRIMARY KEY (`credentials_id`, `email`, `account_id`),
    INDEX `FK_CREDENTIALS_USER_idx` (`email` ASC) VISIBLE,
    INDEX `FK_CREDENTIALS_ACCTOUNT_idx` (`account_id` ASC) VISIBLE,
    CONSTRAINT `FK_CREDENTIALS_USER`
    FOREIGN KEY (`email`)
    REFERENCES `PharmacyManagementSystemDB`.`User` (`email`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `FK_CREDENTIALS_ACCTOUNT`
    FOREIGN KEY (`account_id`)
    REFERENCES `PharmacyManagementSystemDB`.`Account` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
    ENGINE = InnoDB;
    Lzjss
        1
    Lzjss  
    OP
       278 天前
    解决了
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2918 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 11:04 · PVG 19:04 · LAX 03:04 · JFK 06:04
    ♥ Do have faith in what you're doing.