Cannot add Foreign key Constraint! but syntax and pks are correct

Asked

Viewed 273 times

-1

I’m trying to create a FK for a table, I don’t understand syntax error, but the TOAD returns the message that cannot create FK. Tables are created in the database, and 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: Cannot add Foreign key Constraint

I can’t identify the error.

Can someone give me an idea??? Thank you!

mysql database constraint foreign-key

  • Check PK/FK name, type and size

  • CREATE TABLE minsarh.tb_ds_question ( 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.questions add CONSTRAINT fk_questions_perg FOREIGN KEY (id_ds_question) REFERENCES tb_ds_question (id_ds_question) ON DELETE NO ACTION ON UPDATE NO ACTION;

  • So, First thank you for the Wictor reply, but I can’t identify differences, and I’ve tried to change the name of the Constraint, decreasing and still not accepting. I pasted in the previous answer the creation script. if Voce can look.

  • You created FK before adding CONSTRANIT?

1 answer

0

The error is in creating the tables as you put 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 don’t know you didn’t put all the creation but still need to close the CREATE TABLE before starting the ALTER TABLE

In addition the types and sizes of PK and FK should be equal in the first table is defined as INT(11) and the other INT(10)

You are also trying to use a PK of a table as FK of the same table, it doesn’t even make sense

Usually FK are not AUTO-INCREMENT

Browser other questions tagged mysql database constraint foreign-key

You are not signed in. Login or sign up in order to post.