Home MySQL Foreign Key Constraint Error (1215)
Reply: 0

MySQL Foreign Key Constraint Error (1215)

user1893
1#
user1893 Published in April 20, 2018, 10:47 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.570709 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO