1005 - Can’t create table '' Rrno: 150

Question

I’m starting my programming journey and came across a mistake in the creation of the Mysql database:

1005 - Can’t create table 'mydb.supplier' (Rrno: 150)

What could it be? I’ve entered several forums but this same error message appears for various types of errors in Mysql and I still can’t identify the error that is being presented.

The script:

-- -----------------------------------------------------
-- Table `mydb`.`fornecedor`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`fornecedor` (
  `id_fornecedor` INT NOT NULL AUTO_INCREMENT ,
  `id_observ_fornecedor` INT NOT NULL ,
  `nome_fan` VARCHAR(45) NULL ,
  `razao_social` VARCHAR(45) NULL ,
  `cnpj` CHAR(15) NULL ,
  `fone1` CHAR(11) NULL ,
  `fone2` CHAR(11) NULL ,
  `fone3` CHAR(11) NULL ,
  `fone4` CHAR(11) NULL ,
  `email1` VARCHAR(45) NULL ,
  `email2` VARCHAR(45) NULL ,
  `email3` VARCHAR(45) NULL ,
  `endereco` VARCHAR(45) NULL ,
  `ramo_atuacao` VARCHAR(45) NULL ,
  `forn_desd` DATE NULL ,
  `criado_por` INT NULL ,
  `criado_data` DATETIME NULL ,
  `alterado_por` INT NULL ,
  `alterado_data` DATETIME NULL ,
  `deletado_por` INT NULL ,
  `deletado_data` DATETIME NULL ,
  PRIMARY KEY (`id_fornecedor`, `id_observ_fornecedor`) ,
  INDEX `fornecedor_id_observ_idx` (`id_observ_fornecedor` ASC) ,
  INDEX `fk_fornecedor_lista_cot_forn` (`id_fornecedor` ASC) ,
  CONSTRAINT `fk_id_observ`
    FOREIGN KEY (`id_observ_fornecedor` )
    REFERENCES `mydb`.`observ` (`id_observ` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_fornecedor_lista_cot_forn`
    FOREIGN KEY (`id_fornecedor` )
    REFERENCES `mydb`.`lista_cot_forn` (`id_fornecedor_lista_cot_forn` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

This error relates to constraints. Check that the relationships defined in your script already exist and that they are all valid...

  INDEX `fornecedor_id_observ_idx` (`id_observ_fornecedor` ASC) ,
  INDEX `fk_fornecedor_lista_cot_forn` (`id_fornecedor` ASC) ,
  CONSTRAINT `fk_id_observ`
    FOREIGN KEY (`id_observ_fornecedor` )
    REFERENCES `mydb`.`observ` (`id_observ` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_fornecedor_lista_cot_forn`
    FOREIGN KEY (`id_fornecedor` )
    REFERENCES `mydb`.`lista_cot_forn` (`id_fornecedor_lista_cot_forn` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

If you try to create a mandatory relation with another table, and that table does not exist by following these definitions, you will not be able to create your table.

In addition to the existing tables with the expected structure, it is necessary that the data from the other table also meet the expectations.

Answer 2

William’s answer is right. When I write scripts like this to create tables, I first put the tables 'parents', and then write the tables daughters. Therefore, the script will not give these common errors, as the tables and all references and columns will be available.

Tabela Pai

The parent table is the table that will serve as reference in another table. In your case, mydb.observ is a parent table for the table fornecedor.

Table Daughter

The daughter table is the table that has the references, that is, which refers to to the other tables. In your case, the table fornecedor is the daughter table for both observ and lista_cot_forn.

NOTE: A table can be the father of one table and the daughter of another. Any RDBMS (Relational dabaste management system such as Oracle, Mysql, or SQL Server) has this same theory.