Unique Constraints in Doctrine 2, Symfony 2

Question

I want to make a unique constraint in my Doctrine 2 entity such that name & test are unique column wise. Meaning

• obj1

  • name: name1
  • test: test

• obj2

  • name: name2
  • test: test <---- duplicated

This should trigger an error as test is duplicated.

I tried using the unique constraint (Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity). Tried

 * @UniqueEntity("name")
 * @UniqueEntity("test")

and

 * @UniqueEntity({"name", "test"})

Both seem to only trigger error when I have BOTH name and test duplicated. eg.

• obj1

  • name: name1
  • test: test

• obj2

  • name: name2
  • test: test

Whats the right setup? Or I might have made a mistake somewhere?

Perhaps I should include the doctrine annotation like:

@Table(name="ecommerce_products",uniqueConstraints={@UniqueConstraint(name="search_idx", columns={"name", "email"})})

But that still wont handle my symfony form validation I think?

UPDATE

My test code:

/**
 * @ORM\Entity
 * @ORM\Table(name="roles") 
 * @UniqueEntity("name")
 * @UniqueEntity("test")
 */
class Role {

    /**
     * @var integer
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     */
    protected $id;

    /**
     * @var string
     * 
     * @ORM\Column(type="string", length=32, unique=true)
     * @Assert\MaxLength(32)
     * @Assert\Regex("/^[a-zA-Z0-9_]+$/")
     */
    protected $name;

}

$v = $this->get('validator');

$role = new Role();
$role->setName('jm');
$role->setTest('test');
$e = $v->validate($role);
echo '=== 1 ===';
var_dump($e);
if (count($e) == 0)
    $em->persist($role);            

$role2 = new Role();
$role2->setName('john');
$role2->setTest('test');
$e = $v->validate($role2);
echo '=== 2 ===';
var_dump($e);
if (count($e) == 0)
    $em->persist($role2);

$em->flush();

On first run (empty table):

=== 1 ===object(Symfony\Component\Validator\ConstraintViolationList)#322 (1) {
  ["violations":protected]=>
  array(0) {
  }
}
=== 2 ===object(Symfony\Component\Validator\ConstraintViolationList)#289 (1) {
  ["violations":protected]=>
  array(0) {
  }
}

But I do get an error on database layer about unique constraint. So how should I get Validation layer working tho?

Jetty

In your second example, none of the fields are duplicated. It makes your question a bit unclear. – Drumstick

@gre0ire, test is duplicated? – Jetty

Oops sorry. But before, you say "Both". So what is the matter with the second example ? – Drumstick

You should probably append your example with something like "does not trigger any error". – Drumstick

@greg0ire, in the 2nd example, test is duplicated. I expect an validation error. – Jetty

Where is the $test field? I don't see it in the class. – Cassidy

Answer 1

These check for the fields individually:

@UniqueEntity("name")
@UniqueEntity("test")

That is, the first one will get triggered when there is a duplicate name value, while the second one — when there is a duplicate test values.

If you want the validation fail when both name and test contain the same combination, you use this:

@UniqueEntity({"name", "test"})

For what you want the first approach should work — unless you did something wrong somewhere else. Also try to clear the cache to make sure it's not its fault.

UPDATE

What I suggested was about the validation part on the app side. If you generate the database schema using Doctrine, you'll need to supply the Doctrine level annotations for each column — if you want to make them unique independently of each other, of course:

@Column(type = "string", unique = true)
private $name;

@Column(type = "string", unique = true)
private $test;

These approaches complement each other — not exclude. @UniqueEntity makes sure a duplicate doesn't even reach the database layer, while @Column ensures that if it does, the database layer won't let it pass.

Cassidy

Strange thing is for UniqueEntity, it doesn't work! It triggers an error only when both fields duplicate, see my update on main question soon – Jetty

If I wanted to use @UniqueEntity, where in my code should I put it? – Roadability

1 upvote this also explains the difference between @UniqueEntity and @Column(unique=true) – Chant

Answer 2

In the Table annotation, you can also set an index for multiple columns.

/**
 * @ORM\Entity
 * @ORM\Table(name="ecommerce_products",uniqueConstraints={
 *     @ORM\UniqueConstraint(name="search_idx", columns={"name", "email"})})
 */

or with YAML format:

Namespace\Entity\EntityName:
    type: entity
    table: ecommerce_products
    uniqueConstraints:
        uniqueConstraint:
            columns: [name, email]

Rupp

And when the field is a relation to another table? Like private $author. You can't put neither author or author_id in the unique. How do I solve it? – Accompanyist

@hugomn if it s lonked to anothed entity you may add: "myEntity_id". a question to @-yvoyer: how you can add a costum message ? ( or how to get ride of error page ? ) – Lazy

@hugomn, I think that defining the constraint based on the joinned column would still works. ex. Given you have an attribute defined @JoinColumn(name="customer_id", referencedColumnName="id"), specifying the customer_id in the constraint should work... My guess is that the unique constraint is using the definitions from mysql not php. What I mean is that the unique constraint should have the name of the mysql column customer_id, not the PHP attribute $customer. – Rupp

@Timmz, not sure I understand you question about the error pages and custom message? It does not seems to be relevant to the current question. – Rupp

if you want to check for related entities, still put the name of the php or you get that error: The field "country_id" is not mapped by Doctrine, so it cannot be validated for uniqueness.. use this: * @UniqueEntity({"country_id", "product_id"}) with fields: * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Country", inversedBy="products") * @ORM\JoinColumn(name="country_id", referencedColumnName="id", nullable=true, onDelete="set null") */ protected $country; – Groats

This is by far the best answer as unique entity is meant to be used for validation (forms, etc) wherease doctrine constraints are on database level. Both should be used in combination. – Wilding