MySQL Error Code 1215: Can not add foreign key constraint

3

I'm trying to create a database with this script in MySQL But I get error 1215 that can not add the FK constraint I've reviewed but I can not find the problem. Could you help me see the problem?

CREATE DATABASE IF NOT EXISTS mybase;

use mybase;

CREATE TABLE IF NOT EXISTS montos (
    id_monto INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    monto NUMERIC(6 , 2 ) NOT NULL,
    descripcion VARCHAR(100) NOT NULL,
    PRIMARY KEY (id_monto)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;

CREATE TABLE IF NOT EXISTS ciudades (
    id_ciudad INT(10) UNSIGNED NOT NULL auto_increment,
    ciudad VARCHAR(50) NOT NULL,
    provincia VARCHAR(50),
    agencia CHAR(1) NOT NULL,
    PRIMARY KEY (id_ciudad)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;

CREATE TABLE IF NOT EXISTS bancos (
    id_banco INT(10) UNSIGNED NOT NULL auto_increment,
    banco VARCHAR(50) NOT NULL,
    codigo VARCHAR(3),
    PRIMARY KEY (id_banco)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;

CREATE TABLE IF NOT EXISTS asesores (
    id_asesor INT(10) UNSIGNED NOT NULL auto_increment,
    nombre VARCHAR(150) COLLATE UTF8_UNICODE_CI NOT NULL,
    email VARCHAR(150) COLLATE UTF8_UNICODE_CI NOT NULL,
    est_asesor CHAR(1) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_asesor),
    UNIQUE KEY asesor_email_unique (email)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;

CREATE TABLE IF NOT EXISTS cliente (
    cedula VARCHAR(20) NOT NULL,
    tipoID CHAR(1) NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    nacionalidad VARCHAR(20) NOT NULL,
    est_civil CHAR(1) NOT NULL,
    sexo CHAR(1) NOT NULL,
    direccion VARCHAR(255) NOT NULL,
    id_ciudad INT(10) NOT NULL,
    telf_domicilio VARCHAR(20) NOT NULL,
    celular_pers VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    actividad VARCHAR(100),
    profesion VARCHAR(100),
    lugar_trabajo VARCHAR(200),
    cargo VARCHAR(100),
    telefono_t VARCHAR(20),
    celular_t VARCHAR(20),
    direccion_t VARCHAR(255),
    email_t VARCHAR(100),
    ced_conyuge VARCHAR(20),
    nombre_conyuge VARCHAR(255),
    telf_conyuge VARCHAR(20),
    celular_conyuge VARCHAR(20),
    email_conyuge VARCHAR(100),
    nombre_RFamiliar VARCHAR(255),
    telf_RFamiliar VARCHAR(20),
    celular_RF VARCHAR(20),
    nombre_RLaboral VARCHAR(255),
    telf_RLaboral VARCHAR(20),
    celular_RL VARCHAR(20),
    id_banco INT(10),
    tipo_CtaBan CHAR(1),
    num_CtaBan NUMERIC(25),
    ingresosM NUMERIC(30),
    gastosM NUMERIC(30),
    patrimonio NUMERIC(30),
    tipoBien VARCHAR(100),
    observaciones VARCHAR(255),
    PRIMARY KEY (cedula),
    CONSTRAINT fkciudades_id_ciudad FOREIGN KEY (id_ciudad)
        REFERENCES ciudades (id_ciudad),
    CONSTRAINT fkbancos_id_banco FOREIGN KEY (id_banco)
        REFERENCES bancos (id_banco),
    UNIQUE KEY cliente_email_unique (email)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;

CREATE TABLE IF NOT EXISTS contrato (
    p INT NOT NULL,
    g INT NOT NULL,
    o INT NOT NULL,
    cedula VARCHAR(20) NOT NULL,
    contrato INT(50) NOT NULL,
    plazo INT(4) NOT NULL,
    id_monto INT(10) NOT NULL,
    padmin NUMERIC(3) NOT NULL,
    pinsc NUMERIC(3) NOT NULL,
    pdesg NUMERIC(3) NOT NULL,
    padj NUMERIC(3) NOT NULL,
    fing DATE NOT NULL,
    fasam DATE,
    tipo_adj CHAR(1),
    estado_p CHAR(1) NOT NULL,
    estado_c CHAR(1) NOT NULL,
    id_ciudad INT(10) NOT NULL,
    id_asesor INT(10) NOT NULL,
    PRIMARY KEY (p , g , o),
    CONSTRAINT fkcliente_cedula FOREIGN KEY (cedula)
        REFERENCES cliente (cedula),
    CONSTRAINT fkciudades_id_ciudad FOREIGN KEY (id_ciudad)
        REFERENCES ciudades (id_ciudad),
    CONSTRAINT fkasesores_id_asesor FOREIGN KEY (id_asesor)
        REFERENCES asesores (id_asesor),
    CONSTRAINT fkmontos_id_monto FOREIGN KEY (id_monto)
        REFERENCES montos (id_monto)
)  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;

    
asked by Jonathan Ludeña 23.03.2018 в 01:18
source

1 answer

3

The errors you had were:

  • Some columns of the 'client' table had needed to add the     'UNSIGNED'
  •   
  • The contract table changed the     CONSTRAINT         fkciudades_id_ciudad FOREIGN KEY (id_ciudad) REFERENCES ciudad         (id_city)  by     CONSTRAINT fkciudades_id_ciudad_contrato FOREIGN KEY         (id_ciudad) REFERENCES ciudad (id_ciudad)  since they were going to repeat     the restrictions.
  • It would stay like this:

    CREATE TABLE IF NOT EXISTS montos (
        id_monto INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        monto NUMERIC(6 , 2 ) NOT NULL,
        descripcion VARCHAR(100) NOT NULL,
        PRIMARY KEY (id_monto)
    )  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
    
    
    CREATE TABLE IF NOT EXISTS ciudades (
        id_ciudad INT(10) UNSIGNED NOT NULL auto_increment,
        ciudad VARCHAR(50) NOT NULL,
        provincia VARCHAR(50),
        agencia CHAR(1) NOT NULL,
        PRIMARY KEY (id_ciudad)
    )  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
    
    
    CREATE TABLE IF NOT EXISTS bancos (
        id_banco INT(10) UNSIGNED NOT NULL auto_increment,
        banco VARCHAR(50) NOT NULL,
        codigo VARCHAR(3),
        PRIMARY KEY (id_banco)
    )  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
    
    
    CREATE TABLE IF NOT EXISTS asesores (
        id_asesor INT(10) UNSIGNED NOT NULL auto_increment,
        nombre VARCHAR(150) COLLATE UTF8_UNICODE_CI NOT NULL,
        email VARCHAR(150) COLLATE UTF8_UNICODE_CI NOT NULL,
        est_asesor CHAR(1) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id_asesor),
        UNIQUE KEY asesor_email_unique (email)
    )  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
    
    
    CREATE TABLE IF NOT EXISTS cliente (
        cedula VARCHAR(20) NOT NULL,
        tipoID CHAR(1) NOT NULL,
        nombre VARCHAR(255) NOT NULL,
        nacionalidad VARCHAR(20) NOT NULL,
        est_civil CHAR(1) NOT NULL,
        sexo CHAR(1) NOT NULL,
        direccion VARCHAR(255) NOT NULL,
        id_ciudad INT(10) UNSIGNED NOT NULL,
        telf_domicilio VARCHAR(20) NOT NULL,
        celular_pers VARCHAR(20) NOT NULL,
        email VARCHAR(100),
        actividad VARCHAR(100),
        profesion VARCHAR(100),
        lugar_trabajo VARCHAR(200),
        cargo VARCHAR(100),
        telefono_t VARCHAR(20),
        celular_t VARCHAR(20),
        direccion_t VARCHAR(255),
        email_t VARCHAR(100),
        ced_conyuge VARCHAR(20),
        nombre_conyuge VARCHAR(255),
        telf_conyuge VARCHAR(20),
        celular_conyuge VARCHAR(20),
        email_conyuge VARCHAR(100),
        nombre_RFamiliar VARCHAR(255),
        telf_RFamiliar VARCHAR(20),
        celular_RF VARCHAR(20),
        nombre_RLaboral VARCHAR(255),
        telf_RLaboral VARCHAR(20),
        celular_RL VARCHAR(20),
        id_banco INT(10) UNSIGNED,
        tipo_CtaBan CHAR(1),
        num_CtaBan NUMERIC(25),
        ingresosM NUMERIC(30),
        gastosM NUMERIC(30),
        patrimonio NUMERIC(30),
        tipoBien VARCHAR(100),
        observaciones VARCHAR(255),
        PRIMARY KEY (cedula),
        CONSTRAINT fkciudades_id_ciudad FOREIGN KEY (id_ciudad)
            REFERENCES ciudades (id_ciudad),
        CONSTRAINT fkbancos_id_banco FOREIGN KEY (id_banco)
            REFERENCES bancos (id_banco),
        UNIQUE KEY cliente_email_unique (email)
    )  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
    
    
    CREATE TABLE IF NOT EXISTS contrato (
        p INT NOT NULL,
        g INT NOT NULL,
        o INT NOT NULL,
        cedula VARCHAR(20) NOT NULL,
        contrato INT(50) NOT NULL,
        plazo INT(4) NOT NULL,
        id_monto INT(10) UNSIGNED NOT NULL,
        padmin NUMERIC(3) NOT NULL,
        pinsc NUMERIC(3) NOT NULL,
        pdesg NUMERIC(3) NOT NULL,
        padj NUMERIC(3) NOT NULL,
        fing DATE NOT NULL,
        fasam DATE,
        tipo_adj CHAR(1),
        estado_p CHAR(1) NOT NULL,
        estado_c CHAR(1) NOT NULL,
        id_ciudad INT(10) UNSIGNED NOT NULL,
        id_asesor INT(10) UNSIGNED NOT NULL,
        PRIMARY KEY (p,g,o),
       CONSTRAINT fkcliente_cedula FOREIGN KEY (cedula)
            REFERENCES cliente (cedula),
        CONSTRAINT fkciudades_id_ciudad_contrato FOREIGN KEY (id_ciudad)
            REFERENCES ciudades (id_ciudad),
        CONSTRAINT fkasesores_id_asesor_contrato FOREIGN KEY (id_asesor)
            REFERENCES asesores (id_asesor),
        CONSTRAINT fkmontos_id_monto_contrato FOREIGN KEY (id_monto)
            REFERENCES montos (id_monto)
    )  ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
    

    You can see here here

        
    answered by 23.03.2018 в 03:44