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

Answer 4

I have come across this problem some time ago, too. The main problem is not database specific exceptions but the fact, when an PDOException is thrown the EntityManager is closed. That means you can not be sure what will happen with the data you wanted to flush. But probably it would be not saved in database because I think this is done within a transaction.

So when I was thinking about this problem I came up with this solution, but I did not have time to actually write it yet.

1. It could be done using event listeners, particularly the onFlush event. This event is invoked before the data is send to database (after the changesets are computed - so you already know which entities were changed).
2. In this event listener you would have to browse all the changed entities for their keys (for primary it would be looking in the class metadata for @Id).
3. Then you would have to use a find method with the criteria of your keys. If you would find a result, you have the chance to throw your own exception, which will not close the EntityManager and you are able to catch it in your model and make some corrections to the data before trying the flush again.

The problem with this solution would be that it could generate quite a lot of queries to the database, so it would require quite a lot of optimization. If you want to use such thing only in few places I recommend doing the check on the place where the duplicate might arise. So for example where you want to create an entity and save it:

$user = new User('login');
$presentUsers = $em->getRepository('MyProject\Domain\User')->findBy(array('login' => 'login'));
if (count($presentUsers)>0) {
    // this login is already taken (throw exception)
}

Answer 5

If you just want to catch duplicate errors. You should not just check the code number

$e->getCode() === '23000'

because this will catch other errors like field 'user' can not be empty. My solution is to check the error message, if it contains the Text 'Duplicate entry'

                try {
                    $em->flush();
                } catch (\Doctrine\DBAL\DBALException $e) {

                    if (is_int(strpos($e->getPrevious()->getMessage(), 'Duplicate entry'))) {
                        $error = 'The name of the site must be a unique name!';
                    } else {
                        //....
                    }
                }

Answer 6

In Symfony 2, it actually throws an \Exception, not a \PDOException

try {
    // ...
    $em->flush();
}
catch( \Exception $e )
{
   echo $e->getMessage();
   echo  $e->getCode(); //shows '0'
   ### handle ###

}

$e->getMessage() echos something like following:

An exception occurred while executing 'INSERT INTO (...) VALUES (?, ?, ?, ?)' with params [...]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '...' for key 'PRIMARY'

Answer 7

I would like to add to this specifically regarding PDOExceptions--

The 23000 error code is blanket code for a family of Integrity Constraint Violations that MySQL can return.

Therefore, handling the 23000 error code is not specific enough for some use cases.

For example you may want to react differently to a duplicate record violation than to a missing foreign key violation.

Here is an example of how to deal with this:

try {
     $pdo -> executeDoomedToFailQuery();
} catch(\PDOException $e) {
     // log the actual exception here
     $code = PDOCode::get($e);
     // Decide what to do next based on meaningful MySQL code
}

// ... The PDOCode::get function

public static function get(\PDOException $e) {
    $message = $e -> getMessage();
    $matches = array();
    $code = preg_match('/ (\d\d\d\d) / ', $message, $matches);
    return $code;
}

I realize that this is not as detailed as the question was asking but I find this is very useful in many cases and is not Doctrine2 specific.

Answer 8

The easiest way should be this :

$product    = $entityManager->getRepository("\Api\Product\Entity\Product")->findBy(array('productName' => $data['product_name']));
if(!empty($product)){
 // duplicate
}

Answer 9

I used this and it seems to work. It returns the specific MySQL error number - i.e. 1062 for a duplicate entry - ready for you to handle how you like.

try
{
    $em->flush();
}
catch(\PDOException $e)
{
    $code = $e->errorInfo[1];
    // Do stuff with error code
    echo $code;
}

I tested this with a few other scenarios and it will return other codes too like 1146 (Table doesn't exist) and 1054 (Unknown column).