phpmyadmin doesnt allow me to add primary/foreign key relationship

Question

That is the primary table field (tasks table):

task_id int(10)     UNSIGNED    No  None    AUTO_INCREMENT

This is my foreign table field (url_error_stats table):

task_id int(10)     UNSIGNED    No  None

url_error_stats doesnt present the "relation view" option to connect between the keys..why?

SQL query:

ALTER TABLE url_error_stats ADD FOREIGN KEY ( task_id ) REFERENCES aws_backlinks.tasks (

task_id ) ON DELETE CASCADE ON UPDATE CASCADE ;

MySQL said:

1452 - Cannot add or update a child row: a foreign key constraint fails (aws_backlinks., CONSTRAINT #sql-6f0_3bd_ibfk_1 FOREIGN KEY (task_id) REFERENCES tasks (task_id) ON DELETE CASCADE ON UPDATE CASCADE)

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

you have to use innodb and index the primary key if you want to create the foreign keys. and I will recommend you to use NAVICAT . its much easier to create foreign keys and quick too. but for a quick phpmyadmin guide see

Setting up foreign keys in phpMyAdmin?

edited May 23 '17 at 10:33

Community

1
1

answered May 07 '12 at 06:53

Faizan Ali

509
12
35

It works with innodb..but see update on the error that I get..when I connect and use "cascade on delete" – Dmitry Makovetskiyd May 07 '12 at 06:58
have you truncate both tables first ? – Faizan Ali May 07 '12 at 07:03
I need both empty and then connect ...hmm – Dmitry Makovetskiyd May 07 '12 at 07:06
Note that truncating is not needed, see @GarryOne's answer. – mevdschee Mar 20 '19 at 07:00

score 2 · Answer 2 · edited Jan 19 '15 at 16:30

2

Another reason can be the unrelated data in your tables. I mean you may have a foreign key that doesn't exist in parent table.

edited Jan 19 '15 at 16:30

Robert

5,278
43
65
115

answered Jan 19 '15 at 16:13

GarryOne

1,430
17
21

2

Not sure why this was downvoted as it was the solution to my problem – Roshan Bhumbra Sep 14 '16 at 18:47

score 0 · Answer 3 · answered May 07 '12 at 07:12

in this , click on url_error_stats table, then on right hand side it will show all the fields list, so now check on checkbox of that particular field which u wanted to be foreign and click on the link relation view (which is provided by phpmyadmin below to the table fields with blue color hyperlink).

it will open relationship screen , there You can select the field of the primary table. Thanks

phpmyadmin doesnt allow me to add primary/foreign key relationship

3 Answers3