[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MySQL Error 1005 - Foreign Key Constraint

Posted on 2008-06-10
9
Medium Priority
?
1,113 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 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