CODEWITHSUNDEEP
mysqlforeign-keysmysql-workbench
m_callens
m_callens

Reputation: 6360

MySQL Foreign Key Constraint Error (1215)

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.

Upvotes: 1

Views: 44

Answers (1)

Sam M
Sam M

Reputation: 4166

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

Upvotes: 2

Related Questions