SQL Script error - Do not create all the complete tables

Question

SQL Script error - Do not create all the complete tables

Navigation

#1 by (0 votes)

-1

Good afternoon!

I hope you can help me. I am creating a BD for a project. The logical design I did with MySql Workbench and generate the Sql Script to create the database. The issue is that when executing the Script in MySql only creates 6 tables, out of 17 tables that the BD has in total.

I enclose the Sql Script . And the screen print of the tables that it creates.

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';

DROP SCHEMA IF EXISTS 'mydb' ;
CREATE SCHEMA IF NOT EXISTS 'mydb' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE 'mydb' ;

-- -----------------------------------------------------
-- Table 'mydb'.'users'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'users' (
  'id_user' INT NOT NULL AUTO_INCREMENT ,
  'nombre' VARCHAR(100) NOT NULL ,
  'apellido' VARCHAR(100) NOT NULL ,
  'ced_ident' INT NOT NULL ,
  'mail' VARCHAR(50) NOT NULL ,
  'passw' VARCHAR(50) NOT NULL ,
  'type_user' VARCHAR(50) NOT NULL DEFAULT 'user' ,
  'location' VARCHAR(45) NOT NULL ,
  'user_log_in' VARCHAR(45) NULL ,
  PRIMARY KEY ('id_user') )
ENGINE = InnoDB
COMMENT = 'tabla de usuarios principales\n';

CREATE UNIQUE INDEX 'id_user_UNIQUE' ON 'mydb'.'users' ('id_user' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'nros_tlf'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'nros_tlf' (
  'id_nros_tlf' INT NOT NULL AUTO_INCREMENT ,
  'nro_telefonico' INT NULL ,
  'users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_nros_tlf', 'users_id_user') ,
  CONSTRAINT 'fk_nros_tlf_users'
    FOREIGN KEY ('users_id_user' )
    REFERENCES 'mydb'.'users' ('id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_nros_tlf_users_idx' ON 'mydb'.'nros_tlf' ('users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_nros_tlf_UNIQUE' ON 'mydb'.'nros_tlf' ('id_nros_tlf' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'r_social'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'r_social' (
  'id_r_social' INT NOT NULL AUTO_INCREMENT ,
  'user_owner' VARCHAR(50) NOT NULL ,
  'name_r_social' VARCHAR(50) NOT NULL ,
  'rif_r_social' VARCHAR(20) NOT NULL ,
  'location_r_social' VARCHAR(50) NOT NULL ,
  'users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_r_social', 'users_id_user') ,
  CONSTRAINT 'fk_r_social_users1'
    FOREIGN KEY ('users_id_user' )
    REFERENCES 'mydb'.'users' ('id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_r_social_users1_idx' ON 'mydb'.'r_social' ('users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_r_social_UNIQUE' ON 'mydb'.'r_social' ('id_r_social' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'cesta'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'cesta' (
  'id_cesta' INT NOT NULL AUTO_INCREMENT ,
  'ctidad' INT NOT NULL ,
  'status' VARCHAR(45) NULL ,
  'r_social_id_r_social' INT NOT NULL ,
  'r_social_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_cesta', 'r_social_id_r_social', 'r_social_users_id_user') ,
  CONSTRAINT 'fk_cesta_r_social1'
    FOREIGN KEY ('r_social_id_r_social' , 'r_social_users_id_user' )
    REFERENCES 'mydb'.'r_social' ('id_r_social' , 'users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_cesta_r_social1_idx' ON 'mydb'.'cesta' ('r_social_id_r_social' ASC, 'r_social_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_cesta_UNIQUE' ON 'mydb'.'cesta' ('id_cesta' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'status'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'status' (
  'id_status' INT NOT NULL AUTO_INCREMENT ,
  'descripcion' VARCHAR(45) NOT NULL ,
  PRIMARY KEY ('id_status') )
ENGINE = InnoDB;

CREATE INDEX 'fk_status_cesta1_idx' ON 'mydb'.'status' ('cesta_id_cesta' ASC, 'cesta_r_social_id_r_social' ASC, 'cesta_r_social_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_status_UNIQUE' ON 'mydb'.'status' ('id_status' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'carrito'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'carrito' (
  'id_carrito' INT NOT NULL ,
  'items_add' INT NOT NULL ,
  'cantidad' INT NOT NULL ,
  'date_add' VARCHAR(45) NULL ,
  'r_social_id_r_social' INT NOT NULL ,
  'r_social_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_carrito', 'r_social_id_r_social', 'r_social_users_id_user') ,
  CONSTRAINT 'fk_carrito_r_social1'
    FOREIGN KEY ('r_social_id_r_social' , 'r_social_users_id_user' )
    REFERENCES 'mydb'.'r_social' ('id_r_social' , 'users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_carrito_r_social1_idx' ON 'mydb'.'carrito' ('r_social_id_r_social' ASC, 'r_social_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_carrito_UNIQUE' ON 'mydb'.'carrito' ('id_carrito' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'status'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'status' (
  'id_status' INT NOT NULL AUTO_INCREMENT ,
  'descripcion' VARCHAR(45) NOT NULL ,
  PRIMARY KEY ('id_status') )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'mydb'.'status_has_carrito'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'status_has_carrito' (
  'status_id_status' INT NOT NULL ,
  'carrito_id_carrito' INT NOT NULL ,
  'carrito_r_social_id_r_social' INT NOT NULL ,
  'carrito_r_social_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('status_id_status', 'carrito_id_carrito', 'carrito_r_social_id_r_social', 'carrito_r_social_users_id_user') ,
  CONSTRAINT 'fk_status_has_carrito_status1'
    FOREIGN KEY ('status_id_status' )
    REFERENCES 'mydb'.'status' ('id_status' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_status_has_carrito_carrito1'
    FOREIGN KEY ('carrito_id_carrito' , 'carrito_r_social_id_r_social' , 'carrito_r_social_users_id_user' )
    REFERENCES 'mydb'.'carrito' ('id_carrito' , 'r_social_id_r_social' , 'r_social_users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_status_has_carrito_carrito1_idx' ON 'mydb'.'status_has_carrito' ('carrito_id_carrito' ASC, 'carrito_r_social_id_r_social' ASC, 'carrito_r_social_users_id_user' ASC) ;

CREATE INDEX 'fk_status_has_carrito_status1_idx' ON 'mydb'.'status_has_carrito' ('status_id_status' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'compras'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'compras' (
  'id_compras' INT NOT NULL AUTO_INCREMENT ,
  'monto' INT NOT NULL ,
  'date' DATETIME NOT NULL ,
  'carrito_id_carrito' INT NOT NULL ,
  'carrito_r_social_id_r_social' INT NOT NULL ,
  'carrito_r_social_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_compras', 'carrito_id_carrito', 'carrito_r_social_id_r_social', 'carrito_r_social_users_id_user') ,
  CONSTRAINT 'fk_compras_carrito1'
    FOREIGN KEY ('carrito_id_carrito' , 'carrito_r_social_id_r_social' , 'carrito_r_social_users_id_user' )
    REFERENCES 'mydb'.'carrito' ('id_carrito' , 'r_social_id_r_social' , 'r_social_users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_compras_carrito1_idx' ON 'mydb'.'compras' ('carrito_id_carrito' ASC, 'carrito_r_social_id_r_social' ASC, 'carrito_r_social_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_compras_UNIQUE' ON 'mydb'.'compras' ('id_compras' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'posts'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'posts' (
  'id_posts' INT NOT NULL AUTO_INCREMENT ,
  'date_creation' DATETIME NULL DEFAULT 0 ,
  'coments' INT NULL DEFAULT 0 ,
  'likes' INT NULL DEFAULT 0 ,
  'views' INT NULL DEFAULT 0 ,
  'users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_posts', 'users_id_user') ,
  CONSTRAINT 'fk_posts_users1'
    FOREIGN KEY ('users_id_user' )
    REFERENCES 'mydb'.'users' ('id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_posts_users1_idx' ON 'mydb'.'posts' ('users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_posts_UNIQUE' ON 'mydb'.'posts' ('id_posts' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'items'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'items' (
  'id_items' INT NOT NULL AUTO_INCREMENT ,
  'description_item' VARCHAR(100) NOT NULL ,
  'type_item' VARCHAR(45) NOT NULL ,
  'precio' INT NOT NULL ,
  'cant' INT NOT NULL ,
  'vistas' INT NOT NULL DEFAULT 0 ,
  'ventas' INT NOT NULL DEFAULT 0 ,
  'posts_id_posts' INT NOT NULL ,
  'posts_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_items', 'posts_id_posts', 'posts_users_id_user') ,
  CONSTRAINT 'fk_items_posts1'
    FOREIGN KEY ('posts_id_posts' , 'posts_users_id_user' )
    REFERENCES 'mydb'.'posts' ('id_posts' , 'users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_items_posts1_idx' ON 'mydb'.'items' ('posts_id_posts' ASC, 'posts_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_items_UNIQUE' ON 'mydb'.'items' ('id_items' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'pagos'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'pagos' (
  'id_pagos' INT NOT NULL AUTO_INCREMENT ,
  'description_f_pagos' VARCHAR(45) NOT NULL ,
  PRIMARY KEY ('id_pagos') )
ENGINE = InnoDB;

CREATE UNIQUE INDEX 'id_pagos_UNIQUE' ON 'mydb'.'pagos' ('id_pagos' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'pagos_has_items'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'pagos_has_items' (
  'pagos_id_pagos' INT NOT NULL ,
  'items_id_items' INT NOT NULL ,
  'items_posts_id_posts' INT NOT NULL ,
  'items_posts_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('pagos_id_pagos', 'items_id_items', 'items_posts_id_posts', 'items_posts_users_id_user') ,
  CONSTRAINT 'fk_pagos_has_items_pagos1'
    FOREIGN KEY ('pagos_id_pagos' )
    REFERENCES 'mydb'.'pagos' ('id_pagos' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_pagos_has_items_items1'
    FOREIGN KEY ('items_id_items' , 'items_posts_id_posts' , 'items_posts_users_id_user' )
    REFERENCES 'mydb'.'items' ('id_items' , 'posts_id_posts' , 'posts_users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_pagos_has_items_items1_idx' ON 'mydb'.'pagos_has_items' ('items_id_items' ASC, 'items_posts_id_posts' ASC, 'items_posts_users_id_user' ASC) ;

CREATE INDEX 'fk_pagos_has_items_pagos1_idx' ON 'mydb'.'pagos_has_items' ('pagos_id_pagos' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'img'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'img' (
  'id_img' INT NOT NULL AUTO_INCREMENT ,
  'name' VARCHAR(45) NOT NULL ,
  'address' VARCHAR(100) NOT NULL ,
  'ext' VARCHAR(45) NOT NULL ,
  'items_id_items' INT NOT NULL ,
  'items_posts_id_posts' INT NOT NULL ,
  'items_posts_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_img', 'items_id_items', 'items_posts_id_posts', 'items_posts_users_id_user') ,
  CONSTRAINT 'fk_img_items1'
    FOREIGN KEY ('items_id_items' , 'items_posts_id_posts' , 'items_posts_users_id_user' )
    REFERENCES 'mydb'.'items' ('id_items' , 'posts_id_posts' , 'posts_users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_img_items1_idx' ON 'mydb'.'img' ('items_id_items' ASC, 'items_posts_id_posts' ASC, 'items_posts_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_img_UNIQUE' ON 'mydb'.'img' ('id_img' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'ventas'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'ventas' (
  'id_ventas' INT NOT NULL AUTO_INCREMENT ,
  'monto' INT NOT NULL ,
  'fecha_comp' DATETIME NOT NULL ,
  'id_carrito' VARCHAR(45) NOT NULL ,
  PRIMARY KEY ('id_ventas') )
ENGINE = InnoDB;

CREATE UNIQUE INDEX 'id_ventas_UNIQUE' ON 'mydb'.'ventas' ('id_ventas' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'items_has_ventas'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'items_has_ventas' (
  'items_id_items' INT NOT NULL ,
  'items_posts_id_posts' INT NOT NULL ,
  'items_posts_users_id_user' INT NOT NULL ,
  'ventas_id_ventas' INT NOT NULL ,
  PRIMARY KEY ('items_id_items', 'items_posts_id_posts', 'items_posts_users_id_user', 'ventas_id_ventas') ,
  CONSTRAINT 'fk_items_has_ventas_items1'
    FOREIGN KEY ('items_id_items' , 'items_posts_id_posts' , 'items_posts_users_id_user' )
    REFERENCES 'mydb'.'items' ('id_items' , 'posts_id_posts' , 'posts_users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_items_has_ventas_ventas1'
    FOREIGN KEY ('ventas_id_ventas' )
    REFERENCES 'mydb'.'ventas' ('id_ventas' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_items_has_ventas_ventas1_idx' ON 'mydb'.'items_has_ventas' ('ventas_id_ventas' ASC) ;

CREATE INDEX 'fk_items_has_ventas_items1_idx' ON 'mydb'.'items_has_ventas' ('items_id_items' ASC, 'items_posts_id_posts' ASC, 'items_posts_users_id_user' ASC) ;


-- -----------------------------------------------------
-- Table 'mydb'.'comentarios'
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS 'mydb'.'comentarios' (
  'id_comentarios' INT NOT NULL ,
  'fecha' DATETIME NOT NULL ,
  'coments' VARCHAR(45) NOT NULL ,
  'users_id_user' INT NOT NULL ,
  'posts_id_posts' INT NOT NULL ,
  'posts_users_id_user' INT NOT NULL ,
  PRIMARY KEY ('id_comentarios', 'users_id_user', 'posts_id_posts', 'posts_users_id_user') ,
  CONSTRAINT 'fk_comentarios_users1'
    FOREIGN KEY ('users_id_user' )
    REFERENCES 'mydb'.'users' ('id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_comentarios_posts1'
    FOREIGN KEY ('posts_id_posts' , 'posts_users_id_user' )
    REFERENCES 'mydb'.'posts' ('id_posts' , 'users_id_user' )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX 'fk_comentarios_users1_idx' ON 'mydb'.'comentarios' ('users_id_user' ASC) ;

CREATE INDEX 'fk_comentarios_posts1_idx' ON 'mydb'.'comentarios' ('posts_id_posts' ASC, 'posts_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_comentarios_UNIQUE' ON 'mydb'.'comentarios' ('id_comentarios' ASC) ;

USE 'mydb' ;

CREATE USER 'eleazar_super_admin' IDENTIFIED BY '20616897';

GRANT ALL ON 'mydb'.* TO 'eleazar_super_admin';

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

mysql sql script

asked by Eleazar Ortega 24.08.2017 в 15:33

source

1 answer

I update all pivot records problem in AndroidManifest.xml

score 0 · Answer 1

The error that shows you in the image is that you are trying to create an index in a table with some fields that do not exist.

The table in question is this:

-- Table 'mydb.status'

CREATE  TABLE IF NOT EXISTS 'mydb'.'status' (
  'id_status' INT NOT NULL AUTO_INCREMENT ,
  'descripcion' VARCHAR(45) NOT NULL ,
  PRIMARY KEY ('id_status') )
ENGINE = InnoDB;

CREATE INDEX 'fk_status_cesta1_idx' ON 'mydb'.'status' ('cesta_id_cesta' ASC, 'cesta_r_social_id_r_social' ASC, 'cesta_r_social_users_id_user' ASC) ;

CREATE UNIQUE INDEX 'id_status_UNIQUE' ON 'mydb'.'status' ('id_status' ASC) ;

The fk_status_cesta1_idx index is passing you some fields called cesta_id_cesta , cesta_r_social_id_r_social and cesta_r_social_users_id_user that do not exist in this table.

That index does not seem to be in this table.