Symfony Fixtures导入Review表时出现唯一键重复错误求助

问题背景 #

向Symfony项目的Review表导入Fixtures时触发SQL错误：

Fixtures error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '51' for key 'table.UNIQ_794381C688009BCF'

项目其他表已成功填充测试数据，仅Review表为空。

相关代码与结构 #

AppFixtures文件 #

<?php

namespace App\DataFixtures;

use App\Entity\Fiction;
use App\Entity\Review;
use App\Entity\Tags;
use App\Entity\Status;
use App\Entity\User;
use Doctrine\Bundle\FixturesBundle\Fixture;
use Doctrine\Persistence\ObjectManager;
use Faker\Factory as Faker;

class AppFixtures extends Fixture
{
    public function load(ObjectManager $manager): void
    {
        $faker = Faker::create('fr_FR');

        // Creation of Status
        $status = [];

        foreach (['toRead', 'reading', 'read'] as $value) {

            $oneStatus = new Status();

            $oneStatus->setName($value);
            $manager->persist($oneStatus);
            $status[] = $oneStatus;
        } 

        // Creation of 15 Tags
        $multipleTags = [];

        for ($i = 0; $i <15; $i++) {
        
            $tag = new Tags();
            
            $tag->setName($faker->word);

            $manager->persist($tag);
            $multipleTags[] = $tag;
        }

        // Creation of 10 Fictions 
        $fictions = [];

        for ($i = 0; $i <10; $i++) {

            $fiction = new Fiction();

            $fiction->setTitle($faker->sentence);
            $fiction->setAuthor($faker->username);
            $fiction->setSummary($faker->text);
            $fiction->setUrl($faker->url);
            $fiction->setFandom($faker->name);
            $fiction->setPairing($faker->name);
            $fiction->setNbwords($faker->numberBetween(1000,100000));
            $fiction->setNbchapters($faker->numberBetween(1,99));
            $fiction->setStatus($faker->randomElement($status));

            $manager->persist($fiction);
            $fictions[] = $fiction;
            
            // Tags onto Fictions 
            $randomTags = $faker->randomElements($multipleTags, mt_rand(1, 5)); 

            foreach ($randomTags as $tag) {
                $fiction->addFictionTag($tag);
        } 
        }

        // Creation of 10 Users
        $users = []; 
        
        for ($i = 0; $i < 10; $i++) {

            $user = new User();

            $user->setEmail($faker->email);
            $user->setPassword($faker->password);
            $user->setUsername($faker->userName);

            $manager->persist($user);
            $users[] = $user;

        // Association of Users to Fictions (Many to Many relationship)
            foreach ($users as $user) {
                $randomFictions = $faker->randomElements($fictions, mt_rand(1, 3)); 

                foreach ($randomFictions as $fiction) {
                    $fiction->addFictionUser($user);
                    $user->addFiction($fiction);
                }
            }

        // Creation of 1 Review PER User 
        foreach ($users as $user) {
            foreach ($fictions as $fiction) {

                $review = new Review();
                
                $review->setRating($faker->numberBetween(0, 5));
                $review->setComment($faker->text);
                $review->setReviewFiction($fiction);
                $review->setReviewUser($user);
        
                $manager->persist($review);
            }
        }
        $manager->flush();        
}
}
}

Review表SQL结构 #

CREATE TABLE `review` (
  `id` int NOT NULL AUTO_INCREMENT,
  `review_fiction_id` int DEFAULT NULL,
  `review_user_id` int DEFAULT NULL,
  `rating` smallint DEFAULT NULL,
  `comment` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_794381C688009BCF` (`review_fiction_id`),
  KEY `IDX_794381C63C806762` (`review_user_id`),
  CONSTRAINT `FK_794381C63C806762` FOREIGN KEY (`review_user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_794381C688009BCF` FOREIGN KEY (`review_fiction_id`) REFERENCES `fiction` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Review实体代码 #

<?php

namespace App\Entity;

use App\Repository\ReviewRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ReviewRepository::class)]
class Review
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(type: Types::SMALLINT, nullable: true)]
    private ?int $rating = null;

    #[ORM\Column(type: Types::TEXT, nullable: true)]
    private ?string $comment = null;

    #[ORM\OneToOne(inversedBy: 'review', cascade: ['persist', 'remove'])]
    private ?Fiction $reviewFiction = null;

    #[ORM\ManyToOne]
    private ?User $reviewUser = null;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getRating(): ?int
    {
        return $this->rating;
    }

    public function setRating(?int $rating): static
    {
        $this->rating = $rating;

        return $this;
    }

    public function getComment(): ?string
    {
        return $this->comment;
    }

    public function setComment(?string $comment): static
    {
        $this->comment = $comment;

        return $this;
    }

    public function getReviewFiction(): ?Fiction
    {
        return $this->reviewFiction;
    }

    public function setReviewFiction(?Fiction $reviewFiction): static
    {
        $this->reviewFiction = $reviewFiction;

        return $this;
    }

    public function getReviewUser(): ?User
    {
        return $this->reviewUser;
    }

    public function setReviewUser(?User $reviewUser): static
    {
        $this->reviewUser = $reviewUser;

        return $this;
    }
}

问题分析与解决步骤 #

1. 错误根源 #

• 从SQL错误和表结构来看，review_fiction_id字段被设置了唯一约束，意味着每部Fiction只能对应一条Review。
• Fixtures代码中嵌套两层循环：遍历所有User再遍历所有Fiction，会给每部Fiction创建10条Review，直接违反唯一约束。
• Review实体中reviewFiction被定义为OneToOne关联，对应数据库的唯一约束，但实际业务需要的是一个User对一个Fiction只能写一条Review，需调整关联关系。

2. 修复步骤 #

步骤1：修改实体关联关系

将Review实体中的reviewFiction从OneToOne改为ManyToOne，同时添加联合唯一约束，确保一个User对同一个Fiction只能写一条Review：

#[ORM\Entity(repositoryClass: ReviewRepository::class)]
#[ORM\UniqueConstraint(name: 'user_fiction_unique', columns: ['review_user_id', 'review_fiction_id'])]
class Review
{
    // ... 其他代码不变

    // 替换原有的OneToOne为ManyToOne
    #[ORM\ManyToOne(inversedBy: 'reviews')]
    private ?Fiction $reviewFiction = null;

    // ... 其他代码不变
}

同时在Fiction实体中添加对应的反向关联（推荐）：

// 在Fiction实体中添加
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;

#[ORM\OneToMany(mappedBy: 'reviewFiction', targetEntity: Review::class, cascade: ['persist', 'remove'])]
private Collection $reviews;

// 构造函数中初始化
public function __construct()
{
    $this->reviews = new ArrayCollection();
}

// 添加getter方法
public function getReviews(): Collection
{
    return $this->reviews;
}

步骤2：更新数据库结构

执行Symfony命令更新数据库：

php bin/console make:migration
php bin/console doctrine:migrations:migrate

此操作会移除原有的review_fiction_id唯一约束，添加user_id + fiction_id的联合唯一约束。

步骤3：修正Fixtures代码

调整Review生成逻辑，避免重复创建同一User对同一Fiction的Review，同时修复循环嵌套错误：

// Creation of 10 Users
$users = []; 

for ($i = 0; $i < 10; $i++) {
    $user = new User();
    $user->setEmail($faker->email);
    $user->setPassword($faker->password);
    $user->setUsername($faker->userName);
    $manager->persist($user);
    $users[] = $user;
}

// 将用户和小说的关联逻辑移到User循环外面，避免重复执行
// Association of Users to Fictions (Many to Many relationship)
foreach ($users as $user) {
    $randomFictions = $faker->randomElements($fictions, mt_rand(1, 3)); 
    foreach ($randomFictions as $fiction) {
        $fiction->addFictionUser($user);
        $user->addFiction($fiction);
    }
}

// Creation of Reviews: 每个User随机给1-3部Fiction写评论
foreach ($users as $user) {
    $randomFictions = $faker->randomElements($fictions, mt_rand(1, 3)); 
    foreach ($randomFictions as $fiction) {
        $review = new Review();
        $review->setRating($faker->numberBetween(0, 5));
        $review->setComment($faker->text);
        $review->setReviewFiction($fiction);
        $review->setReviewUser($user);
        $manager->persist($review);
    }
}

步骤4：重新运行Fixtures

执行命令重新导入Fixtures：

php bin/console doctrine:fixtures:load --append

若需清空现有数据，去掉--append参数即可。

内容的提问来源于stack exchange，提问作者borderwhirr