Integrity constraint violation errors during migration

I had the same issue when the target site had the same redirect as the source site, but without being migrated. It already existed before the migration. Adding the following event subscriber to a custom module fixed the issue for me, not sure if it's abstract enough to include in the module though:

namespace Drupal\redirect\EventSubscriber;

use Drupal\migrate\Event\MigrateEvents;
use Drupal\migrate\Event\MigratePreRowSaveEvent;
use Drupal\migrate\MigrateException;
use Drupal\migrate\Plugin\MigrateIdMapInterface;
use Drupal\redirect\RedirectRepository;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;

class DuplicateRedirectMigrateSubscriber implements EventSubscriberInterface
{
    public function __construct(
        protected RedirectRepository $redirectRepository,
    ) {
    }

    public static function getSubscribedEvents(): array
    {
        $events[MigrateEvents::PRE_ROW_SAVE][] = ['onPreRowSave'];

        return $events;
    }

    public function onPreRowSave(MigratePreRowSaveEvent $event): void
    {
        $migration = $event->getMigration();
        $destination = $migration->getDestinationPlugin();

        if ($destination->getPluginId() !== 'entity:redirect') {
            return;
        }

        $row = $event->getRow();
        $source = $row->getDestinationProperty('redirect_source');
        $language = $row->getDestinationProperty('language');
        $existingRedirect = $this->redirectRepository->findMatchingRedirect($source['path'], $source['query'] ?? [], $language);

        // Skip this row if the redirect already exists outside the migration.
        if ($existingRedirect && !$migration->getIdMap()->getRowByDestination(['rid' => $existingRedirect->id()])) {
            $sourcePath = $source['path'];
            if (isset($source['query'])) {
                $sourcePath .= '?' . http_build_query($source['query']);
            }

            throw new MigrateException(
                message: sprintf('Redirect already exists for path "%s"', $sourcePath),
                status: MigrateIdMapInterface::STATUS_IGNORED,
            );
        }
    }
}