1005 - Can not create table '' errno: 150

4

I'm starting my journey in programming and I came across a mistake in creating the MySQL database:

  

1005 - Can not create table 'mydb.provider' (errno: 150)

What will it be? I have already logged into several forums but this same error message appears for various types of errors in MySQL and I still can not 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;
    
asked by anonymous 04.02.2014 / 20:59

2 answers

4

This error refers to constraints . Make sure the relationships defined in your script already exist and 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 relationship with another table, and that table does not exist following these settings, you will not be able to create your table.

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

    
04.02.2014 / 21:18
2

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

Pai Table

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 fornecedor table.

Daughter Table

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

NOTE: One table can be the parent 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.

    
05.02.2014 / 18:19