Solved

MySQL Error 1005 - Foreign Key Constraint

Posted on 2008-06-10
9
1,095 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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