Unique Ids in Join table symfony2

Question

I have two entities products and categories, i have successfully created a many-many relationship b/w them, as a result a new table has been created which looks like :

┌────────────────┐         ┌───────────────────┐         ┌────────────────┐
|  products      |         | product_categories|         |     categories |
├────────────────┤         ├───────────────────┤         ├────────────────┤
| id#            ├---------┤ product_id        |    ┌----┤ id#            |
| --             |         | cat_id            ├----┘    | --             |
| --             |         |                   |         |                |
└────────────────┘         └───────────────────┘         └────────────────┘

---

Categories

  /**
 * @ORM\ManyToMany(targetEntity="Products", mappedBy="category")
 */
protected $product;

Products

 /**
 * @ORM\ManyToMany(targetEntity="categories", inversedBy="product")
 * @ORM\JoinTable(name="product_categories",
 *      joinColumns={@ORM\JoinColumn(name="product_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="cat_id", referencedColumnName="id")}
 *      )
 **/
protected $category;

Now i want product and cat_id to be unique in product_categories table. how it can be done ?

Answer 1

If you specify your many-to-many relation implicitly for example through an annotation (@ManyToMany) Doctrine will create composite primary key consists with both fields: product_id and cat_id, so you have a guarantee that it would be unique.

If you specify your many-to-many associations explicitly through additional entity (let's say ProductCategories) you could just add UniqueEntity constraint.

UniqueEntity constraint documentation

An example with annotations:

use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;

/**
 * @ORM\Entity
 * (...)
 * @UniqueEntity(fields={"product_id", "cat_id"})
 */
class ProductCategories {
    // (...)

edit:

In addition make sure that you've generated your database correctly. You should use Doctrine's commands to do that (don't do it manually!):

php -f ./app/console doctrine:database:drop
php -f ./app/console doctrine:database:create
php -f ./app/console doctrine:schema:create 

In that way, based on your entities, Doctrine will create appropriate db schema. If you will change anything in future (in your entities), you'll be able to just update your schema:

php -f ./app/console doctrine:schema:update --force

(with most of above commands you can also add --dump-sql argument which results in showing only sql instead of changing your database)