I'm trying to run a SQL script to generate my database for a project through MySQL workbench but every time I attempt, I keep getting a 1215 error saying that it cannot add the foreign key constraint.
The error was in reference to the relationship below where I'm attempting to have the id of the faculty entity as a foreign key for a grad_program under the director_id field.
-- -----------------------------------------------------
-- Table `jupitercollege`.`faculty`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `jupitercollege`.`faculty` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`dept` VARCHAR(10) NOT NULL,
`email` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `email_UNIQUE` (`email` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `jupitercollege`.`grad_program`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `jupitercollege`.`grad_program` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`dept` VARCHAR(10) NOT NULL,
`phone` VARCHAR(12) NULL DEFAULT 'UNKNOWN',
`email` VARCHAR(45) NULL DEFAULT 'UNKNOWN',
`director_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `director_id_idx` (`director_id` ASC),
UNIQUE INDEX `email_UNIQUE` (`email` ASC),
UNIQUE INDEX `name_UNIQUE` (`name` ASC),
CONSTRAINT `director_id`
FOREIGN KEY (`director_id`)
REFERENCES `jupitercollege`.`faculty` (`id`)
ON DELETE SET NULL
ON UPDATE NO ACTION)
ENGINE = InnoDB;
I've seen a lot of posts about this error but none have really been directly applicable to my scenario, unless I misunderstood them. Any aid in rectifying the situation would be appreciated.
|