Doctrine and composite unique keys

Question

I want to do composite unique key in doctrine. Those are my fields:

/**
 * @var string $videoDimension
 *
 * @Column(name="video_dimension", type="string", nullable=false)
 */
private $videoDimension;

/**
 * @var string $videoBitrate
 *
 * @Column(name="video_bitrate", type="string", nullable=false)
 */
private $videoBitrate;

How can I show doctrine, that those combined together are composite unique key?

score 273 · Accepted Answer · edited Sep 12 '18 at 10:02

273

Answer the question:

use Doctrine\ORM\Mapping\UniqueConstraint;

/**
 * Common\Model\Entity\VideoSettings
 *
 * @Table(name="video_settings", 
 *    uniqueConstraints={
 *        @UniqueConstraint(name="video_unique", 
 *            columns={"video_dimension", "video_bitrate"})
 *    }
 * )
 * @Entity
 */

See @UniqueConstraint

edited Sep 12 '18 at 10:02

Radu

1,159
3
22
40

answered Sep 29 '12 at 07:57

Nikoole

4,553
4
18
23

3

Thanks for the information~ As a note, if you solved your own question, while you can't accept immediately, it's generally good form to accept your own answer, just so if people are searching, it shows as there is an acceptable answer. – Rixius Mar 13 '13 at 22:24
2

Is it possible to do it with `-ToOne` associations (foreign keys)? – Dimitry K Oct 09 '14 at 12:28
5

I know this is old post, but @Dimitry K it is possible. Just you need to use column name as in your @ORM\JoinColumn(name="join_table_id", referencedColumnName="id", nullable=false). Here is 'join_table_id'. – herr Mar 04 '15 at 07:09
1

Note that you have to provide _column_ names, not field names. So you have to convert camelCase to snake_case and append `_id` for associations, because that's how Doctrine generates column names. – gronostaj Mar 06 '18 at 13:35
1

names are autogenerated `@Table(uniqueConstraints={@UniqueConstraint(columns={"case_id", "duration"})})` and nothing else matter – Vasilii Suricov May 02 '19 at 21:54
and after my decision that it can be not unique it looks `@Table(indexes={@Index(columns={"case_id", "duration"})})` – Vasilii Suricov May 02 '19 at 22:01

Az.Youness · Answer 2 · 2021-09-03T17:27:13.680

41

In case someone want use PHP 8 Attributes instead of Doctrine annotations:

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\UniqueConstraint(
  name: 'video_unique_idx',
  columns: ['video_dimension', 'video_bitrate']
)]

edited Sep 03 '21 at 17:27

answered Sep 03 '21 at 17:20

Az.Youness

2,167
1
24
33

score 26 · Answer 3 · edited Apr 23 '20 at 12:16

I find it more verbose to use only ORM and then prefix ORM in annotations. Also note that you can break annotation to several lines to make it more readable especially if you have several items to mention (index in the example below).

use Doctrine\ORM\Mapping as ORM;

/**
 * VideoSettings
 *
 * @ORM\Cache(usage="NONSTRICT_READ_WRITE")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\VideoSettingsRepository")
 * @ORM\Table(name="emails", uniqueConstraints={
 *      @ORM\UniqueConstraint(name="dimension_bitrate", columns={"video_dimension", "video_bitrate"})
 * }, indexes={
 *      @ORM\Index(name="name", columns={"name"})
 * })
 */
class VideoSettings

Stas Parshin · Answer 4 · 2017-07-12T19:54:33.500

I know this is an old question, but I came across it while looking for a way to create composite PK and thought it could use some update.

Things are actually much simpler if what you need is a Composite Primary Key. (Which, of course, guarantees uniqueness) Doctrine documentation contains some nice examples by this url: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html

So the original example could look something like this:

/**
 * @var string $videoDimension
 *
 * @ORM\Id @ORM\Column(type="string")
 */
private $videoDimension;

/**
 * @var string $videoBitrate
 *
 * @ORM\Id @ORM\Column(type="string")
 */
private $videoBitrate;

A few notes here:

Column "name" is omitted since Doctrine is able to guess it based on the property name
Since videoDimension and videoBitrate are both parts of the PK - there is no need to specify nullable = false
If required - the Composite PK may be composed of foreign keys, so feel free to add some relational mappings

What you did is a composite primary key. Sure it will be unique, but it's a primary key... ;) — Preciel, Feb 28 '18 at 14:36
Well yes, I think I've mentioned that in my answer :) Actually the term "unique index" would be more appropriate in OP's case if he didn't mean to create PK (that's what the accepted answer does). But since the question contains a weird term "composite unique key" - I don't see why we can't assume it to be a composite primary key - at least that's what I've been looking for when I came across this question. Cheers! — Stas Parshin, Feb 28 '18 at 20:34

score 3 · Answer 5 · edited Aug 13 '22 at 14:29

3

XML version :

<unique-constraints>
    <unique-constraint columns="column1,column2" name="give_some_explicit_name" />
</unique-constraints>

More details in the docs :
https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/xml-mapping.html#defining-indexes-or-unique-constraints

edited Aug 13 '22 at 14:29

psycho

631
10
27

answered Sep 29 '20 at 09:51

Balmipour

2,985
1
24
28

Doctrine and composite unique keys

5 Answers5

Linked