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

MySQL Foreign Key Constraint Error (1215)

m_callens
1#
m_callens Published in 2017-12-07 21:54:52Z

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.

Sam M
2#
Sam M Reply to 2017-12-07 22:25:31Z

Your child table has ON DELETE SET NULL. MySql 5.6 documentation states

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

Your director_id column has a not null constraint. Remove that constraint or remove the ON DELETE clause.

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO