报错：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;

大佬有話說 (0)