MySQL won't create my modeled database
I'm modeling my database with MySQL Wordbench in a EER Model, which is this :
So after modeling my database I export to a SQL script and try to run it, but it creates only three tables:
Why is that happening ? It shouldn't create all tables ?
This is the generated script when I export:
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';
CREATE SCHEMA IF NOT EXISTS `brainset` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci ;
USE `brainset` ;
-- -----------------------------------------------------
-- Table `brainset`.`departamento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`departamento` (
`ID` TINYINT UNSIGNED NOT NULL ,
`departamento` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`ID`) ,
UNIQUE INDEX `departamento_UNIQUE` (`departamento` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`documento_escopo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`documento_escopo` (
`ID` TINYINT UNSIGNED NOT NULL ,
`escopo` CHAR(7) NOT NULL ,
PRIMARY KEY (`ID`) ,
UNIQUE INDEX `escopo_UNIQUE` (`escopo` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`procedimento_tipo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`procedimento_tipo` (
`ID` TINYINT UNSIGNED NOT NULL ,
`tipo` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`ID`) ,
UNIQUE INDEX `tipo_UNIQUE` (`tipo` ASC) )
ENGINE = InnoDB
COMMENT = ' ';
-- -----------------------------------------------------
-- Table `brainset`.`procedimento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`procedimento` (
`ID` SMALLINT UNSIGNED NOT NULL ,
`nome` VARCHAR(100) NOT NULL ,
`descricao` VARCHAR(1024) NOT NULL ,
`id_tipo` TINYINT UNSIGNED NOT NULL ,
`id_departamento` TINYINT UNSIGNED NOT NULL ,
`id_documento_complementar` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_procedimento-procedimento_tipo` (`id_tipo` ASC, `id_departamento` ASC) ,
INDEX `fk_procedimento-departamento` (`id_departamento` ASC) ,
CONSTRAINT `fk_procedimento-procedimento_tipo`
FOREIGN KEY (`id_tipo` , `id_departamento` )
REFERENCES `brainset`.`procedimento_tipo` (`ID` , `ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_procedimento-departamento`
FOREIGN KEY (`id_departamento` )
REFERENCES `brainset`.`departamento` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`usuario` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nome` VARCHAR(100) NOT NULL ,
`foto` VARCHAR(200) NULL ,
`email` VARCHAR(45) NOT NULL ,
`senha` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`documento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`documento` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`id_procedimento` SMALLINT UNSIGNED NOT NULL ,
`data` DATETIME NOT NULL ,
`revisao` TINYINT NOT NULL ,
`id_escopo` TINYINT UNSIGNED NOT NULL ,
`id_documento_complementar` INT UNSIGNED NULL ,
`id_usuario` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_documento-documento_escopo` (`id_escopo` ASC) ,
INDEX `fk_documento-procedimento` (`id_procedimento` ASC) ,
INDEX `fk_documento-documento` (`id_documento_complementar` ASC) ,
INDEX `fk_documento-usuario` (`id_usuario` ASC) ,
CONSTRAINT `fk_documento-documento_escopo`
FOREIGN KEY (`id_escopo` )
REFERENCES `brainset`.`documento_escopo` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_documento-procedimento`
FOREIGN KEY (`id_procedimento` )
REFERENCES `brainset`.`procedimento` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_documento-documento`
FOREIGN KEY (`id_documento_complementar` )
REFERENCES `brainset`.`documento` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_documento-usuario`
FOREIGN KEY (`id_usuario` )
REFERENCES `brainset`.`usuario` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`questao`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`questao` (
`ID` INT UNSIGNED NOT NULL ,
`questao` VARCHAR(1024) NOT NULL ,
`descricao` VARCHAR(1024) NULL ,
`observacao` VARCHAR(1024) NULL ,
`data` DATETIME NOT NULL ,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`questao_tipo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`questao_tipo` (
`ID` TINYINT UNSIGNED NOT NULL ,
`nome` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`questao_campo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`questao_campo` (
`ID` INT UNSIGNED NOT NULL ,
`id_questao` INT NOT NULL ,
`id_questao_tipo` TINYINT NOT NULL ,
`descricao` VARCHAR(1024) NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_questao_campo-questao` (`id_questao` ASC) ,
INDEX `fk_questao_campo-questao-tipo` (`id_questao_tipo` ASC) ,
CONSTRAINT `fk_questao_campo-questao`
FOREIGN KEY (`id_questao` )
REFERENCES `brainset`.`questao` (`ID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_questao_campo-questao-tipo`
FOREIGN KEY (`id_questao_tipo` )
REFERENCES `brainset`.`questao_tipo` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`questao_escolha`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`questao_escolha` (
`ID` INT UNSIGNED NOT NULL ,
`id_questao_campo` INT NOT NULL ,
`nome` VARCHAR(64) NOT NULL ,
`valor` VARCHAR(64) NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_questao_escolha-questao_campo` (`id_questao_campo` ASC) ,
CONSTRAINT `fk_questao_escolha-questao_campo`
FOREIGN KEY (`id_questao_campo` )
REFERENCES `brainset`.`questao_campo` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`questao_resposta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`questao_resposta` (
`ID` INT UNSIGNED NOT NULL ,
`id_questao` INT NOT NULL ,
`resposta` VARCHAR(1024) NOT NULL ,
`data` DATETIME NOT NULL ,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `brainset`.`questao_consulta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`questao_consulta` (
`ID` INT UNSIGNED NOT NULL ,
`id_usuario` INT NOT NULL ,
`id_questao` INT NOT NULL ,
`id_questao_resposta` INT NOT NULL ,
`data` TIMESTAMP NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_questao_consulta-usuario` (`id_usuario` ASC) ,
INDEX `fk_questao_consulta-questao` (`id_questao` ASC) ,
INDEX `fk_questao_consulta-questao_resposta` (`id_questao_resposta` ASC) ,
CONSTRAINT `fk_questao_consulta-usuario`
FOREIGN KEY (`id_usuario` )
REFERENCES `brainset`.`usuario` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_questao_consulta-questao`
FOREIGN KEY (`id_questao` )
REFERENCES `brainset`.`questao` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_questao_consulta-questao_resposta`
FOREIGN KEY (`id_questao_resposta` )
REFERENCES `brainset`.`questao_resposta` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ' ';
-- -----------------------------------------------------
-- Table `brainset`.`departamento_equipe`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `brainset`.`departamento_equipe` (
`ID` INT UNSIGNED NOT NULL ,
`id_departamento` TINYINT UNSIGNED NOT NULL ,
`id_usuario` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `fk_departamento_equipe-departamento` (`id_departamento` ASC) ,
INDEX `fk_departamento_equipe-usuario` (`id_usuario` ASC) ,
CONSTRAINT `fk_departamento_equipe-departamento`
FOREIGN KEY (`id_departamento` )
REFERENCES `brainset`.`departamento` (`ID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_departamento_equipe-usuario`
FOREIGN KEY (`id_usuario` )
REFERENCES `brainset`.`usuario` (`ID` )
ON DELETE CASCADE
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;
Thanks.
Solution
Just in case anyone stumbles upon this; you can't create a table with a relation to a table that's not created yet.
When MySQL creates a relation it links the table you're creating the relation for to the table you're referencing, if the referencing table doesn't exist, MySQL won't be happy about it.
So when creating a lot of tables that reference each other, make sure that any table that is referenced by another is created before the table that references it.
- MySql can't make column auto_increment
- Insert auto increment primary key to existing table
- 3 mysql table join but not getting expected result that i want
- MySQL insert_id returning null even though there was an insert
- mysql select query within a serialized array
- MySQL: Large VARCHAR vs. TEXT?
- php_network_getaddresses: getaddrinfo failed error in Docker's adminer
- Correlated queries - In derived tables, can I have many nesting levels?
- Is there a MySQL option/feature to track history of changes to records?
- When is a database called as an Embedded database?
- Multiple foreign keys from one table to same field in another table in Laravel
- Datetime-value's time inserted incorrectly in one column but not in another MYSQL
- Syntax diagrams for MySQL
- Converting currency symbols '$' '£' to html code $ £?
- Create Index in mysql not working
- Incorrect string value: '\xEF\xBF\xBD' for column
- Understanding inner join in recursive CTE (MySQL)
- Update MySQL table with rank number of duplicates by name
- Mysql query for 3 tables & user meta
- Setting up new instance of MySQL what is Current Root Password?
- How to change the CHARACTER SET (and COLLATION) throughout a database?
- How to resolve circular foreign key constraints with NOT NULL in Hibernate
- mysql join to record first one which is not matched before
- Conditional statement in migration doesn't work laravel
- Mysql ERROR at line 1153: Unknown command '\'
- How do I find the command causing a replication error in MySQL
- Wordpress Meta Value (Serialized) Filter
- Can you access the auto increment value in MySQL within one statement?
- Can't connect to RDS instance from EC2 instance
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)