error 1005 while creating table

Question

i'm trying to create some db but i get this error : ERROR 1005 (HY000): Can't create table 'ist170438.Composto' (errno: 150)

drop table Encomenda;
drop table Cliente;
drop table Venda;
drop table Disponivel;
drop table Data;
drop table Restaurante;
drop table Prato;
drop table Agregado;
drop table Simples;
drop table Alimento;

create table Alimento( 
  nomeA varchar(30), 
  vegetariano varchar(30),
  primary key (nomeA));

create table Simples( 
  nomeA varchar(30), 
  calgramas double,
  tipo varchar(30),
  primary key (nomeA), 
  foreign key (nomeA) references Alimento(nomeA));

create table Agregado(
    nomeA varchar(30),
    calorias double,
    primary key (nomeA),
    foreign key (nomeA) references Alimento(nomeA));

create table Composto(
    nomeAgg varchar(30),
    nomeS varchar(30),
    quantidade integer,
    primary key(nomeAgg,nomeS),
    foreign key(nomeAgg) references Agregado(nomeAgg),
    foreign key(nomeS) references Simples(nomeS));

can anyone help me finding the eror? i cant understand what is wrong

That's probably because Composto table exists and you forgot to drop it. — Hussain, Nov 08 '13 at 12:38
Yes @Damodaran that too. See http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys — Hussain, Nov 08 '13 at 12:40
Both foreign keys do actually not exist in the referenced table. — Sirko, Nov 08 '13 at 12:43

score 0 · Answer 1 · answered Nov 08 '13 at 13:45

Shouldn't your last table be like this?

CREATE TABLE Composto (
  nomeAgg varchar(30),
  nomeS varchar(30),
  quantidade integer,
  PRIMARY KEY (nomeAgg, nomeS),
  FOREIGN KEY (nomeAgg) REFERENCES Agregado (nomeA),
  FOREIGN KEY (nomeS) REFERENCES Simples (nomeA)
);

error 1005 while creating table

1 Answers1