Error 1005: MySQL - does not create foreign key

Question

Error 1005: MySQL - does not create foreign key

Navigation

#1 by (0 votes)

0

I have a problem with MariaDB.

I have created two tables tbl_estoque and tbl_adiciona .

Basically, I do not want to add data directly to tbl_estoque . I want it to be trigger to check if the last record of tbl_adiciona exists in tbl_estoque . If it exists, it only updates the quantity in inventory, otherwise it adds the record to tbl_estoque .

To do this I find two problems.

The first problem is that I can not basically add a value in cod_produto of tbl_adiciona that is not registered in tbl_estoque .

It usually changes existing records, but does not create a new one.

One way that I used to try to circumvent this problem, would be to reverse the keys (putting the foreign key in tbl_estoque .) Here's a second problem: there can be no duplication of foreign keys, which makes it impossible for me of inserting repeated values in the tbl_adiciona (which at first was meant for this only).

The error that appears is 1005:

Error Code: 1005. Can not create table 'test'. '# sql-1218_5' (errno: 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 do I solve this problem?

mysql mariadb

asked by anonymous 24.09.2017 / 16:36

1 answer

How to implement a PushNotification URL exchange but view maintains - routing with angular 4

score 0 · Answer 1

Make sure the table was created with the InnoDB engine, sometimes it may have an engine that does not allow FK to be created. If you want to test, from drop table tbl_estoque; drop table tbl_adiciona and execute the following instructions. I'm adding the engine at the end of the statement 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;