Error 1005 : Mysql - does not create foreign key

Question

I have a problem with Mariadb.

I created two tables tbl_estoque and tbl_adiciona.

Basically, I don’t want to add data directly to tbl_estoque. I want by means of a trigger, he checks the latest record of the tbl_adiciona exists in the tbl_estoque. If it exists, it only updates the quantity in stock, otherwise it adds the record in the tbl_estoque.

To do this I find two problems.

The first problem is that basically I can’t add a value to cod_produto of tbl_adiciona which is not registered at tbl_estoque.

It normally alters existing records, but does not create a new one.

One way I used to try to dodge this problem, would be by reversing the keys (by placing the foreign key in the tbl_estoque. Here I came across a second problem: there can be no duplication of foreign keys, which makes it impossible for me to insert repeated values in the tbl_adiciona (which at first was designated only for that).

The error that appears is the 1005 :

Error Code: 1005. Can’t create table 'test'. '#sql-1218_5' (Rrno: 150 "Foreign key Constraint is incorrectly Formed")

List of tables :

create table tbl_estoque(
cod_estoque int not null primary key auto_increment,
cod_produto int not null unique,
nome_produto varchar(50) not null, 
quantidade int not null
);

create table tbl_adiciona(
cod_adiciona int not null primary key auto_increment,
cod_produto int not null,
nome_produto varchar(50),
quantidade int not null
);

alter table tbl_estoque add constraint fk_codProduto foreign key(cod_produto) references tbl_adiciona (cod_produto);

How to solve this problem?

Answer 1

Check that the table was created with the engine InnoDB, sometimes may be with an engine that does not allow the creation of FK. If you want to test, a drop table tbl_estoque; drop table tbl_adiciona and perform the following instructions. I am adding the engine at the end of the instruction only, after that, try to create the FK again.

create table tbl_estoque(
cod_estoque int not null primary key auto_increment,
cod_produto int not null unique,
nome_produto varchar(50) not null, 
quantidade int not null
)Engine=InnoDB;

create table tbl_adiciona(
cod_adiciona int not null primary key auto_increment,
cod_produto int not null,
nome_produto varchar(50),
quantidade int not null
)Engine=InnoDB;