Checking for duplicate keys with Doctrine 2
Is there an easy way to check for duplicate keys with Doctrine 2 before doing a flush?
You can catch the UniqueConstraintViolationException
as such:
use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
// ...
try {
// ...
$em->flush();
}
catch (UniqueConstraintViolationException $e) {
// ....
}
I use this strategy to check for unique constraints after flush(), may not be what you want, but might help someone else.
When you call flush(), if a unique constrain fails, a PDOException is thrown with the code 23000.
try {
// ...
$em->flush();
}
catch( \PDOException $e )
{
if( $e->getCode() === '23000' )
{
echo $e->getMessage();
// Will output an SQLSTATE[23000] message, similar to:
// Integrity constraint violation: 1062 Duplicate entry 'x'
// ... for key 'UNIQ_BB4A8E30E7927C74'
}
else throw $e;
}
If you need to get the name of the failing column:
Create table indices with prefixed names, eg. 'unique_'
* @Entity
* @Table(name="table_name",
* uniqueConstraints={
* @UniqueConstraint(name="unique_name",columns={"name"}),
* @UniqueConstraint(name="unique_email",columns={"email"})
* })
DO NOT specify your columns as unique in the @Column definition
This seems to override the index name with a random one...
**ie.** Do not have 'unique=true' in your @Column definition
After you regenerate your table (you may need to drop it & rebuild), you should be able to extract the column name from the exception message.
// ...
if( $e->getCode() === '23000' )
{
if( \preg_match( "%key 'unique_(?P<key>.+)'%", $e->getMessage(), $match ) )
{
echo 'Unique constraint failed for key "' . $match[ 'key' ] . '"';
}
else throw $e;
}
else throw $e;
Not perfect, but it works...
If you're using Symfony2 you can use UniqueEntity(…) with form->isValid()
to catch duplicates prior to flush().
I'm on the fence posting this answer here but it seems valuable since a lot of Doctrine user's will also be using Symfony2. To be clear: this uses Symfony's validations class that under the hood is using an entity repository to check (is configurable but defaults to findBy
).
On your entity you can add the annotation:
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;
/**
* @UniqueEntity("email")
*/
class YourEntity {
Then in your controller, after handing the request to the form you can check your validations.
$form->handleRequest($request);
if ( ! $form->isValid())
{
if ($email_errors = $form['email']->getErrors())
{
foreach($email_errors as $error) {
// all validation errors related to email
}
}
…
I'd recommend combining this with Peter's answer, since your database schema should enforce uniqueness too:
/**
* @UniqueEntity('email')
* @Orm\Entity()
* @Orm\Table(name="table_name",
* uniqueConstraints={
* @UniqueConstraint(name="unique_email",columns={"email"})
* })
*/