Foreign key constraint fails because insert statement not yet committed (MySQL)

Question

I have a PHP script using Doctrine 2 which does essentially the following:

$entityManager->transactional(function($em) { $foreignObject = new DoctrineEntities\ForeignTable(); $em->persist($foreignObject); $em->flush(); $aObject = new DoctrineEntities\A(); $aObject->ForeignID = $foreignObject->ID; $em->persist($aObject); $em->flush(); });

I'm getting an integrity constraint violation:

a foreign key constraint fails (dbName.A, CONSTRAINT A_ForeignID FOREIGN KEY (ForeignID) REFERENCES ForeignTable (ID) ON DELETE NO ACTION ON UPDATE NO ACTION)

My guess is that the constraint is checked before the commit, and it doesn't check to see whether an insert I made that hasn't been committed yet might make the constraint pass rather than fail. But I really do want these two insert statements wrapped in the same transaction. So what can I do?

UPDATE

I moved $em->persist($aObject); $em->flush(); out of the transaction and I'm still getting the same error. Apparently, my guess was wrong... But then I really don't know what's causing the error.

SQL Context

Table A

CREATE TABLE `A` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `ForeignID` int(11) NOT NULL, PRIMARY KEY (`ID`), KEY `A_ForeignID` (`ForeignID`), CONSTRAINT `A_ForeignID` FOREIGN KEY (`ForeignID`) REFERENCES `ForeignTable` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

Table ForeignTable

CREATE TABLE `ForeignTable` ( `ID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

Could you try to make a sample at SQLfiddle? If I'm not mis-reading it sounds like it should "just work". — Joachim Isaksson
– Joachim Isaksson, Commented Nov 5, 2012 at 20:11
@Joachim Isaksson | I've been trying to reproduce this problem strictly in mysql, but I haven't been able to. Maybe the problem is related to the way Doctrine handles transactions..? — Shawn
– Shawn, Commented Nov 5, 2012 at 20:20
Hard to say without seeing the code, but if you're using Doctrine, you may try flushing between the persists, so they don't get reordered. — Joachim Isaksson
– Joachim Isaksson, Commented Nov 5, 2012 at 20:25
I agree, this must be something within your application (or the ORM layer), because the basic order of the statements is fine. — user330315
– user330315, Commented Nov 5, 2012 at 20:27

olegkhuss · Accepted Answer · 2012-11-06 17:45:06Z

I'd suggest to read about MySQL data integrity and FKs, then Doctrine associations, data integrity is checked by MySQL for InnodDB tables. What you're doing is not right, it should be

$entityManager->transactional(function($em) { $foreignObject = new DoctrineEntities\ForeignTable(); $em->persist($foreignObject); $aObject = new DoctrineEntities\A(); $aObject->setForeign($foreignObject); $em->persist($aObject); $em->flush(); });

I am working with an existing database schema. The Doctrine associations link you provide only documents how to use Doctrine to set up associations by creating a new database schema. Do you have any litterature concerning the case where the schema already exists?

Shawn · Accepted Answer · 2012-11-09 21:18:10Z

I solved the problem. It was somewhere else completly unrelated to my question.. I'm going to accept getme's answer instead because accepting this answer really won't help anyone else here...

Collectives™ on Stack Overflow

Foreign key constraint fails because insert statement not yet committed (MySQL)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related