Mysql Doctrine2 foreign key failing

Question

Hi have setup a db with tables States and StoreLocations. I have created entities for both. As well as RESTful services. When posting a new Location on my local machine everything works fine.

I have deployed my project to another server and when I try to post a new Location on this machine I get the following error

Code: 23000 Message: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`vitanica`.`StoreLocation`, CONSTRAINT `storelocation_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`id`)) File: /home/audioglobe.com/zend/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php Line: 131

I do not understand why this would work on one machine and not another. Thanks in advance for any thoughts on the issue!

Here are my tables:

mysql> show create table StoreLocation | StoreLocation | CREATE TABLE `StoreLocation` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(60) DEFAULT NULL, `address` varchar(80) DEFAULT NULL, `city` varchar(80) DEFAULT NULL, `state_id` int(11) DEFAULT NULL, `zip` varchar(12) DEFAULT NULL, `phone` varchar(20) DEFAULT NULL, `lat` float(10,6) DEFAULT NULL, `lng` float(10,6) DEFAULT NULL, PRIMARY KEY (`id`), KEY `state` (`state_id`), CONSTRAINT `storelocation_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 | mysql> show create table State | State | CREATE TABLE `State` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(10) NOT NULL, `state` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 |

And a snippet from my Entities:

/** @Entity */ class State { .... /** @OneToMany(targetEntity="StoreLocation", mappedBy="state") */ private $stores; } /** @Entity @HasLifecycleCallbacks*/ class StoreLocation { /** * @ManyToOne(targetEntity="State", inversedBy="id") */ private $state; }

That often happens when you are not setting one side of a bidirectional entity. Are you creating the State and the StoreLocation at the same time? If not, is the State table populated? — Orbling
– Orbling, Commented Sep 19, 2012 at 20:25
State is fully populated. Just commented out all code inside the post route. When resubmitting I am seeing alot of strange output: — Shawn Northrop
– Shawn Northrop, Commented Sep 19, 2012 at 20:37
b0VIM 7.3�,ZP��#server/zend/routes/storelocator.phputf-8 3210#"! Utp�ghad(�g��R2 ��nP0 � � � } o j e C ! ��kMJ��^ZY#� � � � T � � � j ��(��8�� %$��mEC,��V��{=��}); — Shawn Northrop
– Shawn Northrop, Commented Sep 19, 2012 at 20:38
Not sure where this is coming from I am not running any code inside the route. — Shawn Northrop
– Shawn Northrop, Commented Sep 19, 2012 at 20:39

Shawn Northrop · Accepted Answer · 2012-09-19 23:22:05Z

It seems that the fk was failing because mysql was case-sensitive on production. The table name was State though the constraint referenced state(id). I changed all table names to lower case and added the property @Table('state') to my Entity. Now everything is working as expected! Thanks for the help guys!

NovaDenizen · Accepted Answer · 2012-09-19 20:39:33Z

The gist of the constraint is that, within StoreLocation, you can't have a state_id that is not also present in the State table.

Have you populated your State table with a complete list of states?

Yes I have, Seems like there is another issue that may be causing this problem, see comment above

Collectives™ on Stack Overflow

Mysql Doctrine2 foreign key failing

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related