Problem with my database

Question

Problem with my database

Navigation

#1 by (1 votes)

0

I am trying to create a database for a school project and I get the following error:

(errno: 150 "Foreign key constraint is incorrectly formed")

Please help, the code is as follows:

CREATE SCHEMA IF NOT EXISTS 'bibliotecaFastDev' DEFAULT CHARACTER SET utf8 ;
USE 'bibliotecaFastDev' ;

-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'libro'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'libro' (
  'isbn' VARCHAR(45) NOT NULL,
  'titulo' VARCHAR(45) NOT NULL,
  'n_pags' INT NOT NULL,
  'precio_ref' INT NOT NULL,
  'anio_publ' INT NOT NULL,
  PRIMARY KEY ('isbn'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'editorial'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'editorial' (
  'idEditorial' INT NOT NULL AUTO_INCREMENT,
  'nombre' VARCHAR(45) NOT NULL,
  'Libro_isbn' INT NOT NULL,
  PRIMARY KEY ('idEditorial'),
  INDEX 'fk_Editorial_Libro_idx' ('Libro_isbn' ASC),
  CONSTRAINT 'fk_Editorial_Libro'
    FOREIGN KEY ('Libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'numeroSerie'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'numeroSerie' (
  'n_serie' INT NOT NULL,
  'Libro_isbn' INT NOT NULL,
  PRIMARY KEY ('n_serie'),
  INDEX 'fk_NumeroSerie_Libro1_idx' ('Libro_isbn' ASC),
  CONSTRAINT 'fk_NumeroSerie_Libro1'
    FOREIGN KEY ('Libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'autor'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'autor' (
  'idAutor' INT NOT NULL AUTO_INCREMENT,
  'nombre' VARCHAR(45) NOT NULL,
  'apellidoMat' VARCHAR(45) NOT NULL,
  'apellidoPat' VARCHAR(45) NOT NULL,
  'Libro_isbn' INT NOT NULL,
  PRIMARY KEY ('idAutor'),
  INDEX 'fk_Autor_Libro1_idx' ('Libro_isbn' ASC),
  CONSTRAINT 'fk_Autor_Libro1'
    FOREIGN KEY ('Libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'categoria'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'categoria' (
  'idCategoria' INT NOT NULL AUTO_INCREMENT,
  'nombre' VARCHAR(45) NOT NULL,
  'Libro_isbn' INT NOT NULL,
  PRIMARY KEY ('idCategoria'),
  INDEX 'fk_Categoria_Libro1_idx' ('Libro_isbn' ASC),
  CONSTRAINT 'fk_Categoria_Libro1'
    FOREIGN KEY ('Libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'idioma'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'idioma' (
  'id_idioma' INT NOT NULL AUTO_INCREMENT,
  'idioma' VARCHAR(45) NOT NULL,
  'Libro_isbn' INT NOT NULL,
  PRIMARY KEY ('id_idioma'),
  INDEX 'fk_Idioma_Libro1_idx' ('Libro_isbn' ASC),
  CONSTRAINT 'fk_Idioma_Libro1'
    FOREIGN KEY ('Libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'distribuidor'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'distribuidor' (
  'rut' VARCHAR(11) NULL,
  'nombreEmpresa' VARCHAR(45) NOT NULL,
  'direccion' VARCHAR(45) NOT NULL,
  'anioVenta' INT NOT NULL,
  PRIMARY KEY ('rut'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'cliente'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'cliente' (
  'rut' VARCHAR(11) NOT NULL,
  'nombre' VARCHAR(45) NOT NULL,
  'apellidoMat' VARCHAR(45) NOT NULL,
  'apellidoPat' VARCHAR(45) NOT NULL,
  'fechaNace' DATE NOT NULL,
  PRIMARY KEY ('rut'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'correoCliente'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'correoCliente' (
  'id_correoCliente' INT NOT NULL AUTO_INCREMENT,
  'correo' VARCHAR(65) NOT NULL,
  'cliente_rut' VARCHAR(11) NOT NULL,
  PRIMARY KEY ('id_correoCliente'),
  INDEX 'fk_correoCliente_cliente1_idx' ('cliente_rut' ASC),
  CONSTRAINT 'fk_correoCliente_cliente1'
    FOREIGN KEY ('cliente_rut')
    REFERENCES 'bibliotecaFastDev'.'cliente' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'telefonoCliente'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'telefonoCliente' (
  'id_telefonoCliente' INT NOT NULL AUTO_INCREMENT,
  'telefono' INT NOT NULL,
  'cliente_rut' VARCHAR(11) NOT NULL,
  PRIMARY KEY ('id_telefonoCliente'),
  INDEX 'fk_telefonoCliente_cliente1_idx' ('cliente_rut' ASC),
  CONSTRAINT 'fk_telefonoCliente_cliente1'
    FOREIGN KEY ('cliente_rut')
    REFERENCES 'bibliotecaFastDev'.'cliente' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'trabajador'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'trabajador' (
  'rut' INT NOT NULL,
  'nombre' VARCHAR(45) NOT NULL,
  'apellidoMat' VARCHAR(45) NOT NULL,
  'apellidoPat' VARCHAR(45) NOT NULL,
  'fechaContrato' DATE NOT NULL,
  PRIMARY KEY ('rut'))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'correoTrabajador'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'correoTrabajador' (
  'id_correoTrabajador' INT NOT NULL AUTO_INCREMENT,
  'correo' VARCHAR(65) NOT NULL,
  'trabajador_rut' INT NOT NULL,
  PRIMARY KEY ('id_correoTrabajador'),
  INDEX 'fk_correoTrabajador_trabajador1_idx' ('trabajador_rut' ASC),
  CONSTRAINT 'fk_correoTrabajador_trabajador1'
    FOREIGN KEY ('trabajador_rut')
    REFERENCES 'bibliotecaFastDev'.'trabajador' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'telefonoTrabajador'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'telefonoTrabajador' (
  'id_telefonoTrabajador' INT NOT NULL AUTO_INCREMENT,
  'telefono' INT NOT NULL,
  'trabajador_rut' INT NOT NULL,
  PRIMARY KEY ('id_telefonoTrabajador'),
  INDEX 'fk_telefonoTrabajador_trabajador1_idx' ('trabajador_rut' ASC),
  CONSTRAINT 'fk_telefonoTrabajador_trabajador1'
    FOREIGN KEY ('trabajador_rut')
    REFERENCES 'bibliotecaFastDev'.'trabajador' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'direccionTrabajador'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'direccionTrabajador' (
  'id_direccionTrabajador' INT NOT NULL AUTO_INCREMENT,
  'direccion' VARCHAR(65) NOT NULL,
  'trabajador_rut' INT NOT NULL,
  PRIMARY KEY ('id_direccionTrabajador'),
  INDEX 'fk_direccionTrabajador_trabajador1_idx' ('trabajador_rut' ASC),
  CONSTRAINT 'fk_direccionTrabajador_trabajador1'
    FOREIGN KEY ('trabajador_rut')
    REFERENCES 'bibliotecaFastDev'.'trabajador' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'factura'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'factura' (
  'folio' INT NOT NULL AUTO_INCREMENT,
  'precioNeto' INT NOT NULL,
  'precioMasIVA' INT NOT NULL,
  'costoIVA' INT NOT NULL,
  'fechaCompra' DATE NOT NULL,
  'horaCompra' TIME NOT NULL,
  'distribuidor_rut' VARCHAR(11) NOT NULL,
  PRIMARY KEY ('folio'),
  INDEX 'fk_factura_distribuidor1_idx' ('distribuidor_rut' ASC),
  CONSTRAINT 'fk_factura_distribuidor1'
    FOREIGN KEY ('distribuidor_rut')
    REFERENCES 'bibliotecaFastDev'.'distribuidor' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'metodoPago_factura'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'metodoPago_factura' (
  'id_MetPagoFact' INT NOT NULL AUTO_INCREMENT,
  'metodoPago' VARCHAR(45) NOT NULL,
  'factura_folio' INT NOT NULL,
  PRIMARY KEY ('id_MetPagoFact'),
  INDEX 'fk_metodoPago_factura_factura1_idx' ('factura_folio' ASC),
  CONSTRAINT 'fk_metodoPago_factura_factura1'
    FOREIGN KEY ('factura_folio')
    REFERENCES 'bibliotecaFastDev'.'factura' ('folio')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'boleta'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'boleta' (
  'folio' INT NOT NULL AUTO_INCREMENT,
  'precioNeto' INT NOT NULL,
  'precioMasIVA' INT NOT NULL,
  'costoIVA' INT NOT NULL,
  'fechaVenta' DATE NOT NULL,
  'horaVenta' TIME NOT NULL,
  'cliente_rut' VARCHAR(11) NOT NULL,
  PRIMARY KEY ('folio'),
  INDEX 'fk_boleta_cliente1_idx' ('cliente_rut' ASC),
  CONSTRAINT 'fk_boleta_cliente1'
    FOREIGN KEY ('cliente_rut')
    REFERENCES 'bibliotecaFastDev'.'cliente' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'metodoPago_boleta'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'metodoPago_boleta' (
  'id_MetPagoBol' INT NOT NULL AUTO_INCREMENT,
  'metodoPago' VARCHAR(45) NOT NULL,
  'boleta_folio' INT NOT NULL,
  PRIMARY KEY ('id_MetPagoBol'),
  INDEX 'fk_metodoPago_boleta_boleta1_idx' ('boleta_folio' ASC),
  CONSTRAINT 'fk_metodoPago_boleta_boleta1'
    FOREIGN KEY ('boleta_folio')
    REFERENCES 'bibliotecaFastDev'.'boleta' ('folio')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'arriendo'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'arriendo' (
  'idarriendo' INT NOT NULL AUTO_INCREMENT,
  'costoTotal' INT NOT NULL,
  'fecha' DATE NOT NULL,
  'fechaDevEst' DATE NOT NULL,
  'fechaEntrega' DATE NOT NULL,
  'costo' INT NOT NULL,
  'libro_isbn' VARCHAR(45) NOT NULL,
  'trabajador_rut' INT NOT NULL,
  'cliente_rut' VARCHAR(11) NOT NULL,
  PRIMARY KEY ('idarriendo'),
  INDEX 'fk_arriendo_libro1_idx' ('libro_isbn' ASC),
  INDEX 'fk_arriendo_trabajador1_idx' ('trabajador_rut' ASC),
  INDEX 'fk_arriendo_cliente1_idx' ('cliente_rut' ASC),
  CONSTRAINT 'fk_arriendo_libro1'
    FOREIGN KEY ('libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_arriendo_trabajador1'
    FOREIGN KEY ('trabajador_rut')
    REFERENCES 'bibliotecaFastDev'.'trabajador' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_arriendo_cliente1'
    FOREIGN KEY ('cliente_rut')
    REFERENCES 'bibliotecaFastDev'.'cliente' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'arriendo_multa'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'arriendo_multa' (
  'id_multa' INT NOT NULL AUTO_INCREMENT,
  'diasRetraso' INT NOT NULL,
  'costoExtra' INT NOT NULL,
  'arriendo_idarriendo' INT NOT NULL,
  PRIMARY KEY ('id_multa'),
  INDEX 'fk_arriendo_multa_arriendo1_idx' ('arriendo_idarriendo' ASC),
  CONSTRAINT 'fk_arriendo_multa_arriendo1'
    FOREIGN KEY ('arriendo_idarriendo')
    REFERENCES 'bibliotecaFastDev'.'arriendo' ('idarriendo')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'compra'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'compra' (
  'idcompra' INT NOT NULL AUTO_INCREMENT,
  'factura_folio' INT NOT NULL,
  'distribuidor_rut' VARCHAR(11) NOT NULL,
  PRIMARY KEY ('idcompra'),
  INDEX 'fk_compra_factura1_idx' ('factura_folio' ASC),
  INDEX 'fk_compra_distribuidor1_idx' ('distribuidor_rut' ASC),
  CONSTRAINT 'fk_compra_factura1'
    FOREIGN KEY ('factura_folio')
    REFERENCES 'bibliotecaFastDev'.'factura' ('folio')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_compra_distribuidor1'
    FOREIGN KEY ('distribuidor_rut')
    REFERENCES 'bibliotecaFastDev'.'distribuidor' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'librosComprados'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'librosComprados' (
  'id_librosComprados' INT NOT NULL AUTO_INCREMENT,
  'cantidad' INT NOT NULL,
  'libro_isbn' VARCHAR(45) NOT NULL,
  'compra_idcompra' INT NOT NULL,
  PRIMARY KEY ('id_librosComprados'),
  INDEX 'fk_librosComprados_libro1_idx' ('libro_isbn' ASC),
  INDEX 'fk_librosComprados_compra1_idx' ('compra_idcompra' ASC),
  CONSTRAINT 'fk_librosComprados_libro1'
    FOREIGN KEY ('libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_librosComprados_compra1'
    FOREIGN KEY ('compra_idcompra')
    REFERENCES 'bibliotecaFastDev'.'compra' ('idcompra')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'venta'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'venta' (
  'idventa' INT NOT NULL AUTO_INCREMENT,
  'cliente_rut' VARCHAR(11) NOT NULL,
  'trabajador_rut' INT NOT NULL,
  'boleta_folio' INT NOT NULL,
  PRIMARY KEY ('idventa'),
  INDEX 'fk_venta_cliente1_idx' ('cliente_rut' ASC),
  INDEX 'fk_venta_trabajador1_idx' ('trabajador_rut' ASC),
  INDEX 'fk_venta_boleta1_idx' ('boleta_folio' ASC),
  CONSTRAINT 'fk_venta_cliente1'
    FOREIGN KEY ('cliente_rut')
    REFERENCES 'bibliotecaFastDev'.'cliente' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_venta_trabajador1'
    FOREIGN KEY ('trabajador_rut')
    REFERENCES 'bibliotecaFastDev'.'trabajador' ('rut')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_venta_boleta1'
    FOREIGN KEY ('boleta_folio')
    REFERENCES 'bibliotecaFastDev'.'boleta' ('folio')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'bibliotecaFastDev'.'librosVendidos'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'bibliotecaFastDev'.'librosVendidos' (
  'id_librosVendidos' INT NOT NULL AUTO_INCREMENT,
  'cantidad' INT NOT NULL,
  'venta_idventa' INT NOT NULL,
  'libro_isbn' VARCHAR(45) NOT NULL,
  PRIMARY KEY ('id_librosVendidos'),
  INDEX 'fk_librosVendidos_venta1_idx' ('venta_idventa' ASC),
  INDEX 'fk_librosVendidos_libro1_idx' ('libro_isbn' ASC),
  CONSTRAINT 'fk_librosVendidos_venta1'
    FOREIGN KEY ('venta_idventa')
    REFERENCES 'bibliotecaFastDev'.'venta' ('idventa')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_librosVendidos_libro1'
    FOREIGN KEY ('libro_isbn')
    REFERENCES 'bibliotecaFastDev'.'libro' ('isbn')
    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;

mysql mysql-workbench

asked by Charulo 26.05.2018 в 08:33

source

1 answer

Extract data from the database in columns of three captcha google does not respect js

score 1 · Answer 1

The error is because you declare different types of columns as foreign key.

If you look at bibliotecaFastDev.libro declares isbn VARCHAR(45) NOT NULL then bibliotecaFastDev.editorial declares Libro_isbn INT NOT NULL so it generates an error when creating a foreign key from a column type VARCHAR to another type INT

CONSTRAINT fk_Editorial_Libro 
FOREIGN KEY (Libro_isbn) REFERENCES bibliotecaFastDev.libro (isbn) 
ON DELETE NO ACTION ON UPDATE NO ACTION

And so on all the tables, review them.