Problems in mysql workbench "Error Code: 1005. (errno: 150" Foreign key constraint is incorrectly formed ")"

2

I have this problem in a database for a final school project

  

Error Code: 1005. Can not create table gymn-os . maestros (errno: 150 "Foreign key constraint is incorrectly formed")

Here I leave the script of the database I hope you can help me the truth I do not find anything thanks

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 Gymn-os
-- ----------------------------------------------------- DROP SCHEMA IF EXISTS 'Gymn-os' ;

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

-- -----------------------------------------------------
-- Table 'Persona'
-- ----------------------------------------------------- DROP TABLE IF EXISTS 'Persona' ;

CREATE TABLE IF NOT EXISTS 'Persona' (
    'id_persona' INT UNSIGNED NOT NULL AUTO_INCREMENT,
    'nombre' VARCHAR(50) NOT NULL,
    'apeido_paterno' VARCHAR(50) NOT NULL,
    'apeido_materno' VARCHAR(50) NOT NULL,
    'sexo' VARCHAR(10) NOT NULL,
    'telefono' VARCHAR(25) NOT NULL,
    'fecha_nacimiento' DATE NOT NULL,
    PRIMARY KEY ('id_persona') )  ENGINE=INNODB;

-- -----------------------------------------------------
-- Table 'pago'
-- ----------------------------------------------------- DROP TABLE IF EXISTS 'pago' ;

CREATE TABLE IF NOT EXISTS 'pago' (
    'id_pago' INT NOT NULL AUTO_INCREMENT,
    'pago' INT NOT NULL,
    'fecha_pago' DATE NOT NULL,
    'descuento' INT NOT NULL,
    PRIMARY KEY ('id_pago') )  ENGINE=INNODB;


-- -----------------------------------------------------
-- Table 'Gymn-os'.'Atletas'
-- ----------------------------------------------------- DROP TABLE IF EXISTS 'Gymn-os'.'Atletas' ;

CREATE TABLE IF NOT EXISTS 'Gymn-os'.'Atletas' (
    'matricula' INT NOT NULL AUTO_INCREMENT,
    'nivel' VARCHAR(45) NOT NULL,
    'disciplina' VARCHAR(45) NOT NULL,
    'pago' INT NOT NULL,
    'persona' INT UNSIGNED NOT NULL,
    'tutor' VARCHAR(100) NOT NULL,
    'contacto' VARCHAR(45) NOT NULL,
    PRIMARY KEY ('matricula'),
    INDEX 'pago_idx' ('pago' ASC),
    INDEX 'pers_idx' ('persona' ASC),
    CONSTRAINT 'pago' FOREIGN KEY ('pago')
        REFERENCES 'Gymn-os'.'pago' ('id_pago')
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT 'pers' FOREIGN KEY ('persona')
        REFERENCES 'Gymn-os'.'Persona' ('id_persona')
        ON DELETE CASCADE ON UPDATE CASCADE )  ENGINE=INNODB;

-- -----------------------------------------------------
-- Table 'Gymn-os'.'Maestros'
-- ----------------------------------------------------- DROP TABLE IF EXISTS 'Gymn-os'.'Maestros' ;

CREATE TABLE IF NOT EXISTS 'Gymn-os'.'Maestros' (
    'id_maestro' INT UNSIGNED NOT NULL AUTO_INCREMENT,
    'especialidad' VARCHAR(65) NOT NULL,
    'experiencia' VARCHAR(65) NOT NULL,
    'persona' INT UNSIGNED NOT NULL,
    PRIMARY KEY ('id_maestro'),
    INDEX 'persio_idx' ('persona' ASC),
    CONSTRAINT 'persio' FOREIGN KEY ('persona')
        REFERENCES 'Gymn-os'.'Persona' ('id_persona')
        ON DELETE SET NULL ON UPDATE CASCADE )  ENGINE=INNODB;

-- -----------------------------------------------------
-- Table 'usuarios'
-- ----------------------------------------------------- DROP TABLE IF EXISTS 'usuarios' ;

CREATE TABLE IF NOT EXISTS 'usuarios' (
    'id_usuario' INT NOT NULL AUTO_INCREMENT,
    'contra' VARCHAR(15) NOT NULL,
    'persona' INT UNSIGNED NOT NULL,
    'tipo' VARCHAR(45) NOT NULL,
    PRIMARY KEY ('id_usuario'),
    INDEX 'persona_idx' ('persona' ASC),
    CONSTRAINT 'persona' FOREIGN KEY ('persona')
        REFERENCES 'Persona' ('id_persona')
        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;
    
asked by Diego Pineda Cervantes 25.08.2017 в 23:22
source

1 answer

1

The error is in the creation of the table [Gymn-os]. [Teachers] and it is because your Constraint points to [Gymn-os]. [Person] when there is no such table with its outline.

...
CONSTRAINT 'persio' FOREIGN KEY ('persona') 
REFERENCES 'Persona' ('id_persona')
...

This error is also going to skip you when trying to generate the table of [Gymn-os]. [Athletes]. The one that is correct is the creation of the Users table. Greetings!

Edition: Change the Set Null for Cascade as you have in Gymn-os.Athletes.

    
answered by 26.08.2017 в 01:46