Search code examples
mysqlsqluniqueprimary-keycreate-table

MySQL table creating error


I wanted to create a Date table (Data in portuguese), I had the "unique" attribute in front of dia (day), mes (month) and ano (year), but I decided to remove them, since it makes no sense, because there can be a lot of days "25" and a lot of "januarys" in the table, but as I removed the "unique" attribute I got the following errors

ERROR 1005 (HY000): Can't create table 'ist169515.Disponivel' (errno: 150)
ERROR 1005 (HY000): Can't create table 'ist169515.Venda' (errno: 150)
Query OK, 0 rows affected (0.07 sec)
ERROR 1005 (HY000): Can't create table 'ist169515.Encomenda' (errno: 150)
ERROR 1005 (HY000): Can't create table 'ist169515.RegistoEnc' (errno: 150)

The full code is the following:

create table Alimento
    (nomeA varchar(80) not null unique,
    vegetariano boolean not null,
    PRIMARY KEY(nomeA));

create table Simples
        (nomeA varchar(80) not null,
    calgramas numeric(5,2) not null,
    tipo varchar(255) not null,
    PRIMARY KEY(nomeA),
    FOREIGN KEY(nomeA) REFERENCES Alimento(nomeA));


create table Agregado
        (nomeA varchar(80) not null,
    calorias numeric(5,2) not null,
    PRIMARY KEY(nomeA),
    FOREIGN KEY(nomeA) REFERENCES Alimento(nomeA));

create table Composto
    (nomeAgg varchar(80) not null unique,
    nomeS varchar (80) not null unique,
    quantidade numeric(4),
    PRIMARY KEY(nomeAgg, nomeS),
    FOREIGN KEY(nomeAgg) REFERENCES Agregado(nomeA),
    FOREIGN KEY(nomeS) REFERENCES Simples(nomeA));

create table Prato
    (nomeA varchar(80) not null,
     preco numeric(5,2) not null,
     PRIMARY KEY(nomeA),
     FOREIGN KEY(nomeA) REFERENCES Alimento(nomeA));

create table Restaurante
     (nomeR varchar(80) not null unique,
      PRIMARY KEY(nomeR));

create table Data
     (dia numeric(2) not null,
     mes varchar(45) not null,
     ano numeric(4) not null,
     PRIMARY KEY(dia, mes, ano));

create table Disponivel
     (nomeA varchar(80) not null,
     nomeR varchar(80) not null,
     dia numeric(2) not null,
     mes varchar(45) not null,
     ano numeric(4) not null,
     PRIMARY KEY(nomeA, nomeR, dia, mes, ano),
     FOREIGN KEY(nomeA) REFERENCES Prato(nomeA),
     FOREIGN KEY(nomeR) REFERENCES Restaurante(nomeR),
     FOREIGN KEY(dia) REFERENCES Data(dia),
     FOREIGN KEY(mes) REFERENCES Data(mes),
     FOREIGN KEY(ano) REFERENCES Data(ano));

create table Venda
     (nomeA varchar(80) not null,
     dia numeric(2) not null,
     mes varchar(45) not null,
     ano numeric(4) not null,
     nomeR varchar(80) not null,
     num numeric(5) not null,
     PRIMARY KEY(nomeA, dia, mes, ano, nomeR),
     FOREIGN KEY(nomeA) REFERENCES Prato(nomeA),
     FOREIGN KEY(dia) REFERENCES Data(dia),
     FOREIGN KEY(mes) REFERENCES Data(mes),
     FOREIGN KEY(ano) REFERENCES Data(ano),
     FOREIGN KEY(nomeR) REFERENCES Restaurante(nomeR));

 create table Cliente
     (email varchar(255) not null unique,
     PRIMARY KEY Cliente(email));

 create table Encomenda
     (email varchar(255) not null,
     nEnc numeric(5) not null unique,
     precoTotal numeric(5,2) not null,
     desconto double(20,2) not null,
     nomeR varchar(80) not null,
     dia numeric(2) not null,
     mes varchar(45) not null,
     ano numeric(4) not null,
     PRIMARY KEY(email, nEnc),
     FOREIGN KEY(email) REFERENCES Cliente(email),
     FOREIGN KEY(nomeR) REFERENCES Restaurante(nomeR),
     FOREIGN KEY(dia) REFERENCES Data(dia),
     FOREIGN KEY(mes) REFERENCES Data(mes),
     FOREIGN KEY(ano) REFERENCES Data(ano));

  create table RegistoEnc
     (email varchar(255) not null,
     nEnc numeric(5) not null,
     nomeA varchar(80) not null,
     PRIMARY KEY(email, nEnc, nomeA),
     FOREIGN KEY(email) REFERENCES Encomenda(email),
     FOREIGN KEY(nEnc) REFERENCES Encomenda(nEnc),
     FOREIGN KEY(nomeA) REFERENCES Prato(nomeA));

Solution

  • EDIT:

     FOREIGN KEY(dia, mes, ano) REFERENCES Data(dia, mes, ano)
    

    instead of

     FOREIGN KEY(dia) REFERENCES Data(dia),
     FOREIGN KEY(mes) REFERENCES Data(mes),
     FOREIGN KEY(ano) REFERENCES Data(ano),