CODEWITHSUNDEEP
phpmysqlormdoctrine
user1032531
user1032531

Reputation: 26321

Can Doctrine handle multiple column UNIQUE indexes?

For instance, I have an entity:

/**
 * Serie
 *
 * @ORM\Table(name="series", uniqueConstraints={@ORM\UniqueConstraint(name="unique_position", columns={"chart_id", "position"})})
 */
class Serie
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var int
     *
     * @ORM\Column(name="position", type="integer")
     */
    private $position;

    /**
     * @var Chart
     *
     * @ORM\ManyToOne(targetEntity="Chart", inversedBy="series")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="chart_id", referencedColumnName="id", nullable=false, onDelete="CASCADE")
     * })
     */
    private $chart;
}

with the following schema: 

CREATE TABLE IF NOT EXISTS series (
  id INT(11) NOT NULL AUTO_INCREMENT,
  chart_id INT(11) NOT NULL,
  position INT(11) NOT NULL
  PRIMARY KEY (id),
  UNIQUE INDEX unique_position (chart_id ASC, position ASC) VISIBLE,
  UNIQUE INDEX unique_name (chart_id ASC, name ASC) VISIBLE,
  INDEX IDX_582B2D4DBEF83E0A (chart_id ASC) VISIBLE,
  CONSTRAINT FK_582B2D4DBEF83E0A
    FOREIGN KEY (chart_id)
    REFERENCES chart (id)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

and with the following data:

|----|----------|----------|
| id | position | chart_id |
|----|----------|----------|
| .  | .        | .        |
| .  | .        | .        |
| 3  | 6        | 2        |
| 6  | 1        | 3        |
| 1  | 2        | 3        |
| 4  | 3        | 3        |
| 5  | 4        | 3        |
| 2  | 1        | 4        |
| .  | .        | .        |
| .  | .        | .        |
|----|----------|----------|

When using objects (i.e. not DQL), how can one perform the following for Chart with ID#3:

When I attempt to execute flush(), I get an unique constraint error.

When doing the swap operation without Doctrine, I temporarily store ID#1 position as zero, write ID#5 position to ID#1 position (which no longer violates a unique constraint), and then write the ID#1 position to ID#5 position.

When doing the delete operation without Doctrine, I first delete ID#4 and then reorder using UPDATE series SET position=position-1 WHERE chart_id=3 AND position > 2.

I recognize that maybe there is no way to instruct Doctrine to perform this (especially the swap operation).

Am I able to add some Doctrine listener which just intervenes on select cases and implements what I was doing without Doctrine?

Or maybe one should just not use database level unique indexes and trust Doctrine?

Upvotes: 0

Views: 590

Answers (1)

Michas
Michas

Reputation: 9448

Some databases can check unique constraints at the end of a transaction. In that case the Doctrine ORM works fine. It looks MySQL do not support this feature yet.

Only the database itself can reliably enforce uniqueness. The support for this in the Doctrine is minimal.

The workaround would be to split these operations into two phases.

  1. Delete rows or change positions to negative numbers.
  2. Change positions to the desired numbers.

In a raw SQL it easy to put these operations in one transaction. You can create listener for Doctrine to do so too, but this is much more complicated.

Upvotes: 1

Related Questions