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

报错: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;
2021-05-02 11:57

相关文章

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注