title: How can I make a foreign key with On Delete = Set Null?
body: <p>SOLVED: By removing the primary key atribute from the foreign keys. </p>
  
  <p>I'm building an SQL Database for an school, to keep track of teachers, students, groups and payments. </p>
  
  <p>I have set all the foreign keys to cascade on update (since I believe it's going to be the best way), but I want to set them to null on delete (ie: if someones deletes a group, students can be assigned to another group with an update), but the server gives me this message in tables grupo and grupoalumno when I try to create the database:</p>
  
  <blockquote>
    <p>Error: 150 "Foreign key constraint is incorrectly formed".</p>
  </blockquote>
  
  <p>I have the foreign keys with not null unchecked (which means a foreign key can be a null value), and from what I understood, solution is to set the primary keys being referenced as being able to be null, which makes me question if it would be a good idea to have a PK null.</p>
  
  <p>I have also considered restricting the delete, and only using updates (ie: editing a group until it's right instead of deleting it), but I think being able to actually delete a group without cascading the whole database would be wise.</p>
  
  <p>I'm creating the tables in the order they need to be (ie: not creating a table that references a key that hasn't been created yet.
  <a href="https://i.stack.imgur.com/mAZsZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mAZsZ.jpg" alt="Database Model"></a>  </p>
  
  <p>I'm using PHPMyAdmin and MySQL Workbench. I believe MySQL exports the database with InnoDB as the engine.</p>
  
  <p>I hope I explained myself. Thanks in advance. Hoping to learn a lot!</p>
  
  <p>Code:</p>
  
  <pre><code>-- MySQL Script generated by MySQL Workbench
  -- 10/29/18 21:25:26
  -- Model: New Model    Version: 1.0
  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  
  -- -----------------------------------------------------
  -- Schema celex
  -- -----------------------------------------------------
  CREATE SCHEMA IF NOT EXISTS `celex` DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish_ci ;
  USE `celex` ;
  
  -- -----------------------------------------------------
  -- Table `celex`.`Grupo`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`Grupo` (
    `idGrupo` INT NOT NULL AUTO_INCREMENT,
    `idioma` VARCHAR(45) NOT NULL,
    `nivel` VARCHAR(45) NOT NULL,
    `horarioInicio` TIME NULL,
    `horarioTermino` TIME NULL,
    PRIMARY KEY (`idGrupo`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `celex`.`Alumno`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`Alumno` (
    `idAlumno` INT NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(45) NOT NULL,
    `primerApellido` VARCHAR(45) NOT NULL,
    `segundoApellido` VARCHAR(45) NOT NULL,
    `email` VARCHAR(45) NOT NULL,
    `telefono` VARCHAR(45) NULL,
    `edad` INT NULL,
    `procedencia` VARCHAR(45) NULL,
    `fechaIngreso` DATE NOT NULL,
    `nivelIngreso` VARCHAR(45) NOT NULL,
    `moduloIngreso` INT NOT NULL,
    `nivelColocacion` VARCHAR(45) NULL,
    `moduloColocacion` INT NULL,
    PRIMARY KEY (`idAlumno`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `celex`.`Periodo`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`Periodo` (
    `idPeriodo` INT NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(45) NULL,
    `inicio` DATE NOT NULL,
    `termino` DATE NOT NULL,
    PRIMARY KEY (`idPeriodo`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `celex`.`GrupoAlumno`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`GrupoAlumno` (
    `idGrupoAlumno` INT NOT NULL AUTO_INCREMENT,
    `modulo` INT NOT NULL,
    `calificacionParcial` DOUBLE NULL,
    `calificacionFinal` DOUBLE NULL,
    `calificacionExtra` DOUBLE NULL,
    `Periodo_idPeriodo` INT NULL,
    `Grupo_idGrupo` INT NULL,
    `Alumno_idAlumno` INT NULL,
    PRIMARY KEY (`idGrupoAlumno`, `Periodo_idPeriodo`, `Grupo_idGrupo`, `Alumno_idAlumno`),
    INDEX `fk_Grupo_has_Alumno_Alumno1_idx` (`Alumno_idAlumno` ASC),
    INDEX `fk_Grupo_has_Alumno_Grupo_idx` (`Grupo_idGrupo` ASC),
    INDEX `fk_GrupoAlumno_Periodo1_idx` (`Periodo_idPeriodo` ASC),
    CONSTRAINT `fk_Grupo_has_Alumno_Grupo`
      FOREIGN KEY (`Grupo_idGrupo`)
      REFERENCES `celex`.`Grupo` (`idGrupo`)
      ON DELETE SET NULL
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Grupo_has_Alumno_Alumno1`
      FOREIGN KEY (`Alumno_idAlumno`)
      REFERENCES `celex`.`Alumno` (`idAlumno`)
      ON DELETE SET NULL
      ON UPDATE CASCADE,
    CONSTRAINT `fk_GrupoAlumno_Periodo1`
      FOREIGN KEY (`Periodo_idPeriodo`)
      REFERENCES `celex`.`Periodo` (`idPeriodo`)
      ON DELETE SET NULL
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `celex`.`PeriodoAlumno`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`PeriodoAlumno` (
    `idPeriodoAlumno` INT NOT NULL AUTO_INCREMENT,
    `cuota` TINYINT(1) NULL,
    `Periodo_idPeriodo` INT NULL,
    `Alumno_idAlumno` INT NULL,
    PRIMARY KEY (`idPeriodoAlumno`, `Periodo_idPeriodo`, `Alumno_idAlumno`),
    INDEX `fk_Periodo_has_Alumno_Alumno1_idx` (`Alumno_idAlumno` ASC),
    INDEX `fk_Periodo_has_Alumno_Periodo1_idx` (`Periodo_idPeriodo` ASC),
    CONSTRAINT `fk_Periodo_has_Alumno_Periodo1`
      FOREIGN KEY (`Periodo_idPeriodo`)
      REFERENCES `celex`.`Periodo` (`idPeriodo`)
      ON DELETE SET NULL
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Periodo_has_Alumno_Alumno1`
      FOREIGN KEY (`Alumno_idAlumno`)
      REFERENCES `celex`.`Alumno` (`idAlumno`)
      ON DELETE SET NULL
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `celex`.`Maestro`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`Maestro` (
    `idMaestro` INT NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(45) NOT NULL,
    `primerApellido` VARCHAR(45) NOT NULL,
    `segundoApellido` VARCHAR(45) NOT NULL,
    `email` VARCHAR(45) NOT NULL,
    `telefono` VARCHAR(45) NULL,
    PRIMARY KEY (`idMaestro`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `celex`.`GrupoMaestro`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `celex`.`GrupoMaestro` (
    `idGrupoMaestro` INT NOT NULL AUTO_INCREMENT,
    `Grupo_idGrupo` INT NULL,
    `Maestro_idMaestro` INT NULL,
    PRIMARY KEY (`idGrupoMaestro`, `Grupo_idGrupo`, `Maestro_idMaestro`),
    INDEX `fk_Grupo_has_Maestro_Maestro1_idx` (`Maestro_idMaestro` ASC),
    INDEX `fk_Grupo_has_Maestro_Grupo1_idx` (`Grupo_idGrupo` ASC),
    CONSTRAINT `fk_Grupo_has_Maestro_Grupo1`
      FOREIGN KEY (`Grupo_idGrupo`)
      REFERENCES `celex`.`Grupo` (`idGrupo`)
      ON DELETE SET NULL
      ON UPDATE CASCADE,
    CONSTRAINT `fk_Grupo_has_Maestro_Maestro1`
      FOREIGN KEY (`Maestro_idMaestro`)
      REFERENCES `celex`.`Maestro` (`idMaestro`)
      ON DELETE SET NULL
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  </code></pre>
  
  <p>edit: Appending query to create database.</p>