Symfony2 doctrine association mapping error handling

Question

I get the following error when using php app/console doctrine:schema:update --force.

[Doctrine\DBAL\DBALException]                                                
  An exception occurred while executing 'ALTER TABLE profiles ADD CONSTRAINT   
  FK_8B30853036802B0F FOREIGN KEY (participantid) REFERENCES participants (id  
  )':                                                                          
  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update   
  a child row: a foreign key constraint fails (`smartplan_dev`.`#sql-518_60`,  
   CONSTRAINT `FK_8B30853036802B0F` FOREIGN KEY (`participantid`) REFERENCES   
  `participants` (`id`))

[PDOException]

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update

  a child row: a foreign key constraint fails (smartplan_dev.#sql-518_60,

   CONSTRAINT FK_8B30853036802B0F FOREIGN KEY (participantid) REFERENCES
participants (id))

Does this error occur if there is one instance where the mapping fails? I can see this being problematic since my clients like to edit directly using mysql workbench. Is there a way to just skip the rows that fail and not fail the whole update.

Here is my entity files in question. (profiles is partially done).

namespace classes\classBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * profiles
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class profiles
{
    /** 
     * 
     * @ORM\ManyToOne(targetEntity="participants", inversedBy="profiles")
     * @ORM\JoinColumn(name="participantid", referencedColumnName="id")
     */
    public $participant;//participant variable
    /**
     * @ORM\OneToMany(targetEntity="profilesBeneficiaries", mappedBy="profile")
     * 
     */
    public $beneficiaries;//beneficiaries array variable
    public $contributions; //contributions variable
    public $investments; //investments array variable
    public $retirementNeeds; //retirementNeeds variable
    public $riskProfile;//riskProfile variable
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public $id;
    /**
     * @var integer
     *
     * @ORM\Column(name="participantid", type="integer")
     */
    public $participantid;   
    /**
     * @var integer
     *
     * @ORM\Column(name="userid", type="integer")
     */
    public $userid;
    /**
     * @var string
     *
     * @ORM\Column(name="planid", type="integer")
     */
    public $planid;
    /**
     * @var string
     *
     * @ORM\Column(name="planName", type="string", length=255)
     */
    public $planName;
    /**
     * @var string
     *
     * @ORM\Column(name="currentBalance", type="float",length = 255)
     */
    public $currentBalance;
    /**
     * @var string
     *
     * @ORM\Column(name="beneficiaryStatus", type="text",length = 65535)
     */
    public $beneficiaryStatus;
    /**
     * @var string
     *
     * @ORM\Column(name="investmentsStatus", type="text",length = 65535)
     */
    public $investmentsStatus;
    /**
     * @var string
     *
     * @ORM\Column(name="realignmentStatus", type="text",length = 65535)
     */
    public $realignmentStatus;
    /**
     * @var string
     *
     * @ORM\Column(name="contributionsStatus", type="text",length = 65535)
     */
    public $contributionsStatus;
    /**
     * @var string
     *
     * @ORM\Column(name="catchupContributionStatus", type="text",length = 65535)
     */
    public $catchupContributionStatus;
    /**
     * @var string
     *
     * @ORM\Column(name="annualSalary", type="float",length = 255)
     */
    public $annualSalary;
     /**
     * @var string
     *
     * @ORM\Column(name="retireAge", type="integer")
     */
    public $retireAge;
     /**
     * @var string
     *
     * @ORM\Column(name="preTaxSavingRate", type="float",length = 255)
     */
    public $preTaxSavingRate;
     /**
     * @var string
     *
     * @ORM\Column(name="postTaxSavingRate", type="float",length = 255)
     */
    public $postTaxSavingRate;
     /**
     * @var string
     *
     * @ORM\Column(name="rothTaxSavingRate", type="float",length = 255)
     */
    public $rothTaxSavingRate;
     /**
     * @var string
     *
     * @ORM\Column(name="mStarContribution", type="text",length = 65535)
     */
    public $mStarContribution;
     /**
     * @var string
     *
     * @ORM\Column(name="mStarStatus", type="text",length = 65535)
     */
    public $mStarStatus;
     /**
     * @var string
     *
     * @ORM\Column(name="smart401kStatus", type="text",length = 65535)
     */
    public $smart401kStatus;
     /**
     * @var string
     *
     * @ORM\Column(name="reportDate", type="datetime")
     */
    public $reportDate;
     /**
     * @var string
     *
     * @ORM\Column(name="profileId", type="string", length = 50)
     */
    public $profileId;
     /**
     * @var string
     *
     * @ORM\Column(name="acceptedAdvice", type="text",length = 65535)
     */
    public $acceptedAdvice;
     /**
     * @var string
     *
     * @ORM\Column(name="ACAOptOutStatus", type="text",length = 65535)
     */
    public $ACAOptOutStatus;
     /**
     * @var string
     *
     * @ORM\Column(name="profilestatus", type="smallint")
     */
    public $profilestatus;
     /**
     * @var string
     *
     * @ORM\Column(name="availability", type="string",length = 250)
     */
    public $availability;
     /**
     * @var string
     *
     * @ORM\Column(name="clientCalling", type="string",length = 50)
     */
    public $clientCalling;
     /**
     * @var string
     *
     * @ORM\Column(name="mstarQuit", type="smallint" )
     */
    public $mstarQuit;
     /**
     * @var string
     *
     * @ORM\Column(name="rkdData", type="text",length = 65535 )
     */
    public $rkdData;
     /**
     * @var string
     *
     * @ORM\Column(name="welcomeVideo", type="string",length = 63 )
     */
    public $welcomeVideo;
     /**
     * @var string
     *
     * @ORM\Column(name="planBasicVideo", type="string",length = 127 )
     */
    public $planBasicVideo;
     /**
     * @var string
     *
     * @ORM\Column(name="uniqid", type="string",length = 63 )
     */
    public $uniqid;
     /**
     * @var string
     *
     * @ORM\Column(name="migration", type="smallint" )
     */
    public $migration;
}

namespace classes\classBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * participants
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class participants
{

    /**
     * @ORM\OneToMany(targetEntity="profiles", mappedBy="participant")
     * 
     */
    public $profiles;//array of profiles


    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public $id;
    /**
     * @var string
     *
     * @ORM\Column(name="firstName", type="blob", length = 127)
     */
    public $firstName;
    /**
     * @var string
     *
     * @ORM\Column(name="lastName", type="blob", length = 127)
     */
    public $lastName;
    /**
     * @var string
     *
     * @ORM\Column(name="legalName", type="blob", length = 255)
     */
    public $legalName;
    /**
     * @var string
     *
     * @ORM\Column(name="address", type="blob", length = 255)
     */
    public $address;
    /**
     * @var string
     *
     * @ORM\Column(name="city", type="string", length = 50)
     */
    public $city;
    /**
     * @var string
     *
     * @ORM\Column(name="state", type="string", length = 50)
     */
    public $state;
    /**
     * @var string
     *
     * @ORM\Column(name="zip", type="string", length = 10)
     */
    public $zip;
    /**
     * @var string
     *
     * @ORM\Column(name="maritalStatus", type="blob", length = 63)
     */
    public $maritalStatus;
    /**
     * @var string
     *
     * @ORM\Column(name="employmentDate", type="blob", length = 127)
     */
    public $employmentDate;
    /**
     * @var string
     *
     * @ORM\Column(name="birthDate", type="blob", length = 127)
     */
    public $birthDate;
    /**
     * @var string
     *
     * @ORM\Column(name="employeeId", type="blob", length = 255)
     */
    public $employeeId;
    /**
     * @var string
     *
     * @ORM\Column(name="email", type="blob", length = 255)
     */
    public $email;

     /**
     * @var string
     *
     * @ORM\Column(name="phone", type="string",length = 50)
     */
    public $phone;
}

turntwo turntwo · Accepted Answer · 2014-11-16T15:21:49

Yeah it fails because an entry or number of entries in your schema violates your foreign key constraint. This has nothing to do with your mapping or doctrine ORM. This is an sql returned error and no you can't force it to skip any failed entries because doctrine is just an abstraction layer for your database. In any case, this is the whole point of foreign keys, to preserve data integrity.

Sorry man but I feel it's my responsibility to tell you that it would be in your best interest to find those table entries that are violating your constraint and deal with them accordingly. The next time your client tries to do anything with the database he won't be able to unless he knows what exactly it is he's doing which is how it should be.

Symfony2 doctrine association mapping error handling

1 Answers