Solved

MySQL Error 1005 - Foreign Key Constraint

Posted on 2008-06-10
9
1,100 Views
Last Modified: 2012-06-27
I am trying to add a foreign key constraint between two tables. Primary table is "test_req_info" and foreign table is "test_network". Both the keys have same data type. The only difference is that "pk" in "test_req_info" is set to auto_inc and "req_pk" in foreign table is not set to auto_inc. Will this make a difference?

 It gives me the following error. SQL used is shown below

ALTER TABLE `srf`.`test_req_info` ADD CONSTRAINT `FK_test_req_info_1` FOREIGN KEY `FK_test_req_info_1` (`pk`)
    REFERENCES `test_network` (`req_pk`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
Can't create table\...\(erno :150)
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21754689
are the 2 tables InnoDb?
are the 2 fields both the EXACT same data type, size, nullable?
0
 

Author Comment

by:xoxomos
ID: 21754758
Sorry Angel,I did not know it was you .The two tables are InnoDB. However, for "PK" in "test_req_info" table it sets Default value to Null even though "Not Null"option is picked. Do you think this might be causing a problem.?Is there a way I can undo that Default value?

Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21754785
the "default" should not matter.
can you post the create table script of the 2 tables, anyhow?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:xoxomos
ID: 21755350
CREATE TABLE  `srf`.`test_network` (
  `pk` int(10) unsigned NOT NULL auto_increment,
  `req_pk` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `srf`.`test_req_info` (
  `pk` int(10) unsigned NOT NULL auto_increment,
  `req_netid` varchar(45) NOT NULL,
  PRIMARY KEY  (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 21756706
ALTER TABLE `srf`.`test_req_info` ADD CONSTRAINT `FK_test_req_info_1` FOREIGN KEY `FK_test_req_info_1` (`pk`)
    REFERENCES `test_network` (`req_pk`)

cannot be right. the REFERENCES must point to a field that is PRIMARY or UNIQUE KEY on the references table. test_network.req_pk is not primary key, PK is.

I guess that you want this, hence
ALTER TABLE `srf`.`test_network` ADD CONSTRAINT `FK_test_req_info_1` FOREIGN KEY `FK_test_req_info_1` (`req_pk`)
    REFERENCES `test_req_info` (`pk`)
 
 

Open in new window

0
 

Author Comment

by:xoxomos
ID: 21761697
Would that be more like:

************* child table  

CREATE TABLE `srf`.`test_network`
(
  `pk` int(10) unsigned NOT NULL auto_increment,
  `req_pk` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Describe Orders;


***********   primary table *********************


CREATE TABLE  `srf`.`test_req_info` (
  `pk` int(10) unsigned NOT NULL auto_increment,
  `req_netid` varchar(45) NOT NULL,
  FOREIGN KEY  (`pk`) REFERENCES test_network  (req_pk)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
0
 

Author Comment

by:xoxomos
ID: 21762020
Thanks Angel. That actually works :)
0
 

Author Closing Comment

by:xoxomos
ID: 31465917
Thanks Again!
0
 

Author Comment

by:xoxomos
ID: 21762038
So you really are a savior! :-)
0

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question