CODEWITHSUNDEEP
phpmysqlsqlsymfonydoctrine-orm
Abel
Abel

Reputation: 538

Symfony 2 Doctrine constrain foreign key issue with the mysql Database

I am trying to create a one to many relationship between Vehicle and job as follows: 

use Doctrine\ORM\Mapping as ORM;


/**
 * @ORM\Entity
 * @ORM\Table(name="job")
 */
class Job
{
/**
 * @ORM\Column(type="integer", name="id_vehicle")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $idJob;
/**
 * @ORM\Column(type="string", length=20, name="delivery_number")
 */
protected $deliveryNo; 
/**
 * @ORM\Column(type="datetime", name="date_time")
 */
protected $dateTime;
/**
 * @ORM\Column(type="string", length=200, name="destination")
 */
protected $destination;      
/**
 * @ORM\Column(type="integer", name="km_odo_start", nullable=true)
 */    
protected $kmOdoStart;   
/**
 * @ORM\Column(type="integer", name="km_odo_end", nullable=true)
 */    
protected $kmOdoEnd;      
/**
 * @ORM\Column(type="string", length=50, name="deliveryType", nullable=true)
 */
protected $deliveryType;
/**
 * @ORM\Column(type="integer", name="fuel_used", nullable=true) 
 */
protected $fuelUsedLitre;
/**
 * @ORM\Column(type="string", length=100, name="driver_name", nullable=true)
 */    
protected $driverName;
/**
 * @ORM\Column(type="string", length=250, name="crew_names", nullable=true)
 */    
protected $crewNames;
/**
 * @ORM\Column(type="string", length=7, name="triler_plate_number", nullable=true)
 */
protected $trilerPlateNumber;
/**
 * @ORM\Column(type="string", length=500, name="remarks")
 */
protected $remarks;
/**
 * @ORM\Column(type="string", length=500, name="return_load_plan")
 */
protected $returnLoadPlan;
/**
* @ORM\ManyToOne(targetEntity="Vehicle", inversedBy="job")
* @ORM\JoinColumn(name="id_vehicle", referencedColumnName="idVehicle")
*/
protected $idVehicle;    
}

And the vehicle: 

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
/**
 * @ORM\Entity
 * @ORM\Table(name="vehicle")
 */

class Vehicle
{
/**
 * @ORM\Column(type="integer", name="id_vehicle")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $idVehicle;
/**
 * @ORM\Column(type="string", length=7, name="plate_number")
 */
protected $plateNumber;

/**
 * @ORM\Column(type="integer", name="distance_to_service")
 */ 
protected $DistanceToServiceKm;
/**
 * @ORM\Column(type="integer", name="last_service_odo")
 */ 
protected $lastServiceODOKm; 
/**
 * @ORM\Column(type="string", length=100, name="make")
 */
protected $makeName;   
/**
 * @ORM\Column(type="string", length=100, name="model")
 */
protected $modelName;

/**
* @ORM\OneToMany(targetEntity="Job", mappedBy="vehicle")
*/
protected $jobs;

public function __construct()
{
    $this->jobs = new ArrayCollection();
}

When I try to run:

PS D:\web\wamp\www\team> php app/console doctrine:schema:update --force
Updating database schema...

  [Doctrine\DBAL\DBALException]
  An exception occurred while executing 'ALTER TABLE job ADD CONSTRAINT FK_FBD8E0F8C51D4DF6 FOREIGN KEY (id_vehicle)
  REFERENCES vehicle (idVehicle)':

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint


  [PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint



doctrine:schema:update [--complete] [--dump-sql] [--force] [--em[="..."]]


PS D:\web\wamp\www\team>

Then the --dump-sql generates good MySQL code. What do you think is happening? I this a bug or I am making a mistake? I have seen a few threads like this, but none of the things I've seen works for me. any help will be appreciated. Regards

Upvotes: 1

Views: 9759

Answers (3)

Javad
Javad

Reputation: 4397

Two small issues

in the job class

/**
* @ORM\ManyToOne(targetEntity="Vehicle", inversedBy="jobs")
* @ORM\JoinColumn(name="id_vehicle", referencedColumnName="id_vehicle")
*/
protected $vehicles; //changed the name to different one just for no conflict

In the vehicle class

/**
* @ORM\OneToMany(targetEntity="Job", mappedBy="vehicles")
*/
protected $jobs;

The referencedColumnName should the column name of related field not FieldName; so it should be id_vehicle.
Second was in mappedBy, it should be set as the property name which is vehicles

Just in case, check your table structure through phpMyAdmin or any other tools if you have and check if the field with the foreign key was created try to drop the field and foreign key then apply these changes and run the update command

Upvotes: 3

Wcool
Wcool

Reputation: 329

I think you must replace the idVehicle variable in the Job entity to vehicle, because you're telling the Vehicle entity that jobs are mapped to vehicle (not idVehicle).

edit: Was still typing when another answer already rolled out, my bad.

edit2: @Benny, I think that the referencedColumnName must be idVehicle and not id, as in the Vehicle entity, there is no id field (the UID is idVehicle).

Upvotes: 0

Benny
Benny

Reputation: 1465

You are using the ID of the vehicle in the Job class. Use the Object itself, doctrine will map it automatically to the id in the database.

/**
 * @ORM\ManyToOne(targetEntity="Vehicle", inversedBy="job")
 * @ORM\JoinColumn(name="id_vehicle", referencedColumnName="id")
 */
protected $vehicle;

That makes Doctrine know there should be an object of type "Vehicle" in that field and makes Doctrine create a column "id_vehicle" in the Job table that maps vehicles to the "id" column of the vehicle table.

Upvotes: -1

Related Questions