I put the fields in minuscule but Error 1215 still appears. Can not add foreign key constraint

1

I'm trying to run the script exported from a relational model I did using MySQL WorkBench , but when I try to create the bd I see an error, which I searched, and it tells me that it is due because the name of some fields are in capital letters, because I put them in minuscule and it is still going out, I will leave here the code and an image of the bd.

SQL Script

-- MySQL Script generated by MySQL Workbench
-- 09/07/17 14:40:25
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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 mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS 'mydb' DEFAULT CHARACTER SET utf8 ;
USE 'mydb' ;

-- -----------------------------------------------------
-- Table 'mydb'.'Roles'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Roles' (
  'idRoles' INT NOT NULL,
  'tipoRol' VARCHAR(15) NULL,
  PRIMARY KEY ('idRoles'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Personas'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Personas' (
  'idPersonas' INT NOT NULL,
  'nombres_Persona' VARCHAR(50) NULL,
  'apellidos_Persona' VARCHAR(50) NULL,
  PRIMARY KEY ('idPersonas'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Usuarios'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Usuarios' (
  'username' VARCHAR(50) NOT NULL,
  'password' VARCHAR(50) NOT NULL,
  'Roles_idRoles' VARCHAR(15) NOT NULL,
  'Personas_idPersonas' INT NOT NULL,
  PRIMARY KEY ('username'),
  INDEX 'fk_Usuarios_Roles_idx' ('Roles_idRoles' ASC),
  INDEX 'fk_Usuarios_Personas1_idx' ('Personas_idPersonas' ASC),
  CONSTRAINT 'fk_Usuarios_Roles'
    FOREIGN KEY ('Roles_idRoles')
    REFERENCES 'mydb'.'Roles' ('idRoles')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_Usuarios_Personas1'
    FOREIGN KEY ('Personas_idPersonas')
    REFERENCES 'mydb'.'Personas' ('idPersonas')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Facultades'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Facultades' (
  'idFacultades' INT NOT NULL,
  'nombreFacultad' VARCHAR(50) NULL,
  PRIMARY KEY ('idFacultades'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Programas'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Programas' (
  'idProgramas' INT NOT NULL,
  'nombrePrograma' VARCHAR(50) NULL,
  'Facultades_idFacultades' INT NOT NULL,
  PRIMARY KEY ('idProgramas'),
  INDEX 'fk_Programas_Facultades1_idx' ('Facultades_idFacultades' ASC),
  CONSTRAINT 'fk_Programas_Facultades1'
    FOREIGN KEY ('Facultades_idFacultades')
    REFERENCES 'mydb'.'Facultades' ('idFacultades')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Asignatura'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Asignatura' (
  'idAsignatura' VARCHAR(10) NOT NULL,
  'nombre_Asignatura' VARCHAR(45) NULL,
  'creditos' INT NULL,
  'Programas_idProgramas' INT NOT NULL,
  PRIMARY KEY ('idAsignatura'),
  INDEX 'fk_Asignatura_Programas1_idx' ('Programas_idProgramas' ASC),
  CONSTRAINT 'fk_Asignatura_Programas1'
    FOREIGN KEY ('Programas_idProgramas')
    REFERENCES 'mydb'.'Programas' ('idProgramas')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Grupos'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Grupos' (
  'idGrupos' INT NOT NULL,
  'numeroGrupo' INT NOT NULL,
  'Asignatura_idAsignatura' VARCHAR(10) NOT NULL,
  PRIMARY KEY ('idGrupos'),
  INDEX 'fk_Grupos_Asignatura1_idx' ('Asignatura_idAsignatura' ASC),
  CONSTRAINT 'fk_Grupos_Asignatura1'
    FOREIGN KEY ('Asignatura_idAsignatura')
    REFERENCES 'mydb'.'Asignatura' ('idAsignatura')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Calificaciones'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Calificaciones' (
  'idCalificaciones' INT NOT NULL,
  'nota_1' FLOAT NULL,
  'nota_2' FLOAT NULL,
  'nota_3' FLOAT NULL,
  'Habilitacion' FLOAT NULL,
  'Grupos_idGrupos' INT NOT NULL,
  PRIMARY KEY ('idCalificaciones'),
  INDEX 'fk_Calificaciones_Grupos1_idx' ('Grupos_idGrupos' ASC),
  CONSTRAINT 'fk_Calificaciones_Grupos1'
    FOREIGN KEY ('Grupos_idGrupos')
    REFERENCES 'mydb'.'Grupos' ('idGrupos')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Horario'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Horario' (
  'lugar' VARCHAR(45) NULL,
  'dia' VARCHAR(45) NULL,
  'hora' VARCHAR(45) NULL,
  'Grupos_idGrupos' INT NOT NULL,
  INDEX 'fk_Horario_Grupos1_idx' ('Grupos_idGrupos' ASC),
  CONSTRAINT 'fk_Horario_Grupos1'
    FOREIGN KEY ('Grupos_idGrupos')
    REFERENCES 'mydb'.'Grupos' ('idGrupos')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'Personas_Grupos'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'mydb'.'Personas_Grupos' (
  'Personas_idPersonas' INT NOT NULL,
  'Grupos_idGrupos' INT NOT NULL,
  PRIMARY KEY ('Personas_idPersonas', 'Grupos_idGrupos'),
  INDEX 'fk_Personas_has_Grupos_Grupos1_idx' ('Grupos_idGrupos' ASC),
  INDEX 'fk_Personas_has_Grupos_Personas1_idx' ('Personas_idPersonas' ASC),
  CONSTRAINT 'fk_Personas_has_Grupos_Personas1'
    FOREIGN KEY ('Personas_idPersonas')
    REFERENCES 'mydb'.'Personas' ('idPersonas')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_Personas_has_Grupos_Grupos1'
    FOREIGN KEY ('Grupos_idGrupos')
    REFERENCES 'mydb'.'Grupos' ('idGrupos')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Relational model

    
asked by Pedro Miguel Pimienta Morales 07.09.2017 в 21:52
source

1 answer

4

The error is in the definition of the field Roles_idRoles in the table Usuarios .

To be able to create a foreign key the data type of Usuarios.Roles_idRoles , it must be equal to Roles.idRoles .

Solution:

Modify this

'Roles_idRoles' VARCHAR(15) NOT NULL,

To this

'Roles_idRoles' INT NOT NULL,

Complete table code:

CREATE TABLE IF NOT EXISTS 'mydb'.'Usuarios' (
  'username' VARCHAR(50) NOT NULL,
  'password' VARCHAR(50) NOT NULL,
  'Roles_idRoles' INT NOT NULL,
  'Personas_idPersonas' INT NOT NULL,
  PRIMARY KEY ('username'),
  INDEX 'fk_Usuarios_Roles_idx' ('Roles_idRoles' ASC),
  INDEX 'fk_Usuarios_Personas1_idx' ('Personas_idPersonas' ASC),
  CONSTRAINT 'fk_Usuarios_Roles'
    FOREIGN KEY ('Roles_idRoles')
    REFERENCES 'mydb'.'Roles' ('idRoles')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_Usuarios_Personas1'
    FOREIGN KEY ('Personas_idPersonas')
    REFERENCES 'mydb'.'Personas' ('idPersonas')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
    
answered by 07.09.2017 / 22:12
source