Relationship between tables n for n

0

I'm creating a small project and I'm having a question.

Suppose I have a certain types of coffees, these coffees can be served in more than two cup sizes. Since the relationship between tables is from n to n , we have the following tables:

The SQL statements are:

create table cafes (id_cafe int auto_increment not null, nome_cafe varchar(30) not null,
primary key (id_cafe));

create table xicara (id_xicara int auto_increment not null, nome_xicara varchar(30) not null,
primary key (id_xicara));

create table xicara_cafe (id_cafe int, id_xicara int,
primary key (id_cafe, id_xicara));

alter table xicara_cafe foreign key (id_cafe) references cafes(id_cafe);

alter table xicara_cafe foreign key (id_xicara) references xicara(id_xicara);

It turns out that it is giving error exactly in the relationship between the tables. I wanted the tip for this impasse.

    
asked by anonymous 23.12.2016 / 05:46

1 answer

1

Formalizing the response (quoted in Havenard's comment), the ADD was missing before the foreign key command:

alter table xicara_cafe add foreign key (id_cafe) references cafes(id_cafe);

alter table xicara_cafe add foreign key (id_xicara) references xicara(id_xicara);

You can also do directly by creating the table:

create table xicara_cafe (id_cafe int, id_xicara int,
primary key (id_cafe, id_xicara),
foreign key(id_cafe) references cafes(id_cafe),
foreign key(id_xicara) references xicara(id_xicara));

Documentation for SQL FOREIGN KEY .

    
23.12.2016 / 12:00