MySQL Error 1005 - Foreign Key Constraint

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)
xoxomosAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are the 2 tables InnoDb?
are the 2 fields both the EXACT same data type, size, nullable?
0
 
xoxomosAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "default" should not matter.
can you post the create table script of the 2 tables, anyhow?
0
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
Thanks Angel. That actually works :)
0
 
xoxomosAuthor Commented:
Thanks Again!
0
 
xoxomosAuthor Commented:
So you really are a savior! :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.