Checking for duplicate keys with Doctrine 2

Question

Is there an easy way to check for duplicate keys with Doctrine 2 before doing a flush?

Answer 1

You can catch the UniqueConstraintViolationException as such:

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;

// ...

try {
   // ...
   $em->flush();
}
catch (UniqueConstraintViolationException $e) {
    // ....
}

Answer 2

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...

Answer 3

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"})
 * })
 */