Solved

MySQL Error 1005 - Foreign Key Constraint

Posted on 2008-06-10
9
1,096 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 73
Using Modal's in to Retrieve Data from MySql and Populate Forms 13 87
Accessing variables in MySQL query 4 57
Mysql how to execute a commands file 5 33
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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