CODEWITHSUNDEEP
mysqldoctrine-orm
Shawn Northrop
Shawn Northrop

Reputation: 6016

Mysql Doctrine2 foreign key failing

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;
}

Upvotes: 1

Views: 990

Answers (2)

Shawn Northrop
Shawn Northrop

Reputation: 6016

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!

Upvotes: 3

NovaDenizen
NovaDenizen

Reputation: 5305

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?

Upvotes: 0

Related Questions