Problems adding Foreign key to Mysql

Question

I’m having trouble defining the attribute anoAuto from the business table as a foreign key for cars.ano. Mysql always says "ERROR CODE: 1215. Cannot add Foreign key Constraint".

create table automoveis (
codigo int,
ano int,
fabricante varchar(20),
modelo varchar(20),
pais varchar(20),
precoTabela decimal(8, 2),
primary key (codigo, ano));

create table revendedoras (
cgc int,
nome varchar(30),
proprietário varchar(30),
cidade varchar(30),
estado varchar(30),
primary key(cgc));

create table consumidores (
identidade char(7),
nome varchar(30),
sobrenome varchar(30),
primary key(identidade));

create table negocios (
comprador char(7),
revenda int,
codAuto int,
anoAuto int,
data_compra date,
preco decimal(8,2),
primary key (comprador, revenda, codAuto, anoAuto),
foreign key (comprador) references consumidores(identidade),
foreign key (revenda) references revendedoras(cgc),
foreign key (codAuto) references automoveis(codigo),
foreign key (anoAuto) references automoveis(ano));

Answer 1

In the car would add the plate as data uniqueness.

create table automoveis (
  placa char(7),
  codigo int,
  ano int,
  fabricante varchar(20),
  modelo varchar(20),
  pais varchar(20),
  precoTabela decimal(8, 2),
  primary key (placa),
  unique index (codigo, ano)
);

In business an id for it, and a unique index to avoid combination duplicity.

create table negocios (
  id int auto_increment,
  comprador char(7),
  revenda int,
  automovel char(7),
  data_compra date,
  preco decimal(8,2),
  primary key (id),
  foreign key (comprador) references revendedoras(identidade),
  foreign key (revenda) references revendedoras (cgc),
  foreign key (automovel) references automoveis (placa),
  unique index (comprador, revenda, automovel)
);

I hope it helps something...

Answer 2

Ever tried to add a constraint?

Constraints are basically rules you put in a table, like Primary Key, Unique, Foreign Key, etc..
The syntax for foreign key would be something like:

alter table tabela add constraint nome_da_constraint foreign key(nome_do_campo)
references tabela_estrangeira(nome_do_campo_da_tabela_estrangeira);

More information :

Constraints - Mysql

Answer 3

Here, in the table deals:

foreign key (anoAuto) references automoveis(ano))

attribute year does not uniquely define a row of auto table.

I don’t know what you’re up to. Maybe your foreign key should be composed (codAuto, anoAuto).