Can not add foreign key constraint! however syntax and pks are correct

Question

Can not add foreign key constraint! however syntax and pks are correct

Navigation

#1 by (0 votes)

0

I'm trying to create an FK for a table, I do not understand syntax error, but TOAD returns me the message that FK can not create. The tables are created in the database, and the PKs are right.

ALTER TABLE minsarh.perguntas ADD CONSTRAINT fk_perg_perg FOREIGN KEY (id_ds_pergunta) 
 REFERENCES minsarh.tb_ds_pergunta (id_ds_pergunta) ON UPDATE NO ACTION ON DELETE NO ACTION;

Looup Error - MySqk Database Error: Can not add foreign key constraint

I can not identify the error.

Can anyone give me an idea ???? Thanks!

mysql constraint foreign-key

asked by anonymous 25.04.2018 / 18:32

1 answer

Which theoretical distribution family do you use for indexes? Keyboard down arrow with typeahead

score 0 · Answer 1

The error is in creating the tables as you put them in the comments:

CREATE TABLE minsarh.tb_ds_pergunta ( 
    id_ds_pergunta int(11) unsigned NOT NULL AUTO_INCREMENT ,
    ds_pergunta varchar(255) NOT NULL ,
    PRIMARY KEY (id_ds_pergunta) 
); 

CREATE TABLE perguntas ( 
    id_pergunta int(10) unsigned NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (id_pergunta), 

Alter Table minsarh.perguntas add CONSTRAINT fk_perguntas_perg FOREIGN KEY (id_ds_pergunta) REFERENCES tb_ds_pergunta (id_ds_pergunta) ON DELETE NO ACTION ON UPDATE NO ACTION;

I do not know, you did not put all the creation but you have to close CREATE TABLE before starting ALTER TABLE

In addition, the types and sizes of PK and FK must be the same in the first table as INT(11) and in the other INT(10)

You're also having to use a PK from a table as FK from the same table, that does not even make sense

Generally FK are not AUTO-INCREMENT