MySQL Bugs: #47708: Cannot add or update a child row: a foreign key constraint fails

Bug #47708	Cannot add or update a child row: a foreign key constraint fails
Submitted:	29 Sep 2009 11:06	Modified:	30 Oct 2009 7:22
Reporter:	MySQL DBA	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.30-ndb-6.3.20-cluster-gpl	OS:	Linux (Fedora 8)
Assigned to:		CPU Architecture:	Any
Tags:	Mysql foreign key constraint

Description:
Respected Sirs,

I have created table Test and MyTest. Test is my parent table and MyTest is a child table. When I am trying to create foreign key relationship between parent and child after creation of table it is giving me following error:

Cannot add or update a child row: a foreign key constraint fails (`MyDB`.`#sql-bab_548`, CONSTRAINT `FK_MyTest_Test` FOREIGN KEY (`FK_TestID`) REFERENCES `Test` (`TestID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Also my TestID column is of Varchar data type.

But, my other table has got no problem while creating this relationship.
Also both these tables are  empty right now. so there is no data in these table right now.

Can anyone provide any input on this why this is giving me this error. Is it a bug or is there any other workaround for this error.

Thanks in advance.
Regards,
Manasi 
MySQL DBA

How to repeat:
Parent table Definition :-

CREATE TABLE  `Test` (
  `TestID` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`TestID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Child Table Definition :-

CREATE TABLE  `MyTest` (
  `MyTestID` int(10) unsigned NOT NULL,
  `FK_TestID` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY(`MyTestID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE MyTest
ADD CONSTRAINT FK_MyTest_Test
FOREIGN KEY FK_MyTest_Test(FK_TestID)
REFERENCES Test(TestID)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

Suggested fix:
I could not resolve this error. So I am checking this constraint at procedure level while inserting record in child table whether the record exists in parent table.

I can not repeat this with 5.1.39:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.39-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE  `Test` (
    ->   `TestID` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
    ->   PRIMARY KEY (`TestID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE  `MyTest` (
    ->   `MyTestID` int(10) unsigned NOT NULL,
    ->   `FK_TestID` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
    ->   PRIMARY KEY(`MyTestID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> ALTER TABLE MyTest
    -> ADD CONSTRAINT FK_MyTest_Test
    -> FOREIGN KEY FK_MyTest_Test(FK_TestID)
    -> REFERENCES Test(TestID)
    -> ON DELETE NO ACTION
    -> ON UPDATE NO ACTION;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

So, please, upgrade and inform about the results. If you need NDB, try 5.1.35-ndb-7.0.7 maybe.

Hi Valeriy Kravchuk,

Thanks for quick response.

I tried the same thing with 5.1.35-ndb-7.0.7-cluster-gpl. But it gave me same error.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`MyDB`.`#sql-4975_4e6`, CONSTRAINT `FK_MyTest_Test` FOREIGN KEY (`FK_TestID`) REFERENCES `Test` (`TestID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Is there any possible other problem I should check.

Thanks,

Thank you for the feedback.

Please turn on option \W (show warnings) in mysql command line client, then run test case again in your environment. Please send us output: I want to check if you catch some warnings.

Hi Sveta,

While creating table its a normal message. It executes fine.
Query OK, 0 rows affected (0.01 sec)

Then after when I try to add foreign key constraint then now it is giving me an error as follows:

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1005 | Can't create table 'MyDB.#sql-4975_4e7' (errno: 150) |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

Thanks in advance.

As the problem is not repeatable with 5.1.139, maybe you should try some NDB binaries based on newer 5.1 codebase. Check 5.1.37-ndb-7.0.8, please.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".