时间戳

首页 > 解决方案 > Add Unique Index to Existing Laravel 8 Migration Table When Duplicates In Database Exist

php - Add Unique Index to Existing Laravel 8 Migration Table When Duplicates In Database Exist

问题描述

My Laravel 8 Project has a table that has many duplicates in the database. I want to add unique to the table but I'm getting migration error SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Non-Haz Waste' for key 'streams.streams_stream_name_unique' (SQL: alter table 'streams' add unique 'streams_stream_name_unique'('stream_name')). The error is because of the existing duplicates.

Below is my migration file to add unique which produces the error. Is there way to still add unique to my table? If not, I also placed my create function that does validate and show an on-screen error that the name exists, but it creates it in the database anyway.

I'd prefer the unique to be part of the index, but would settle for my addStream function not adding the duplicate.

2021_11_03_163710_add_unique_to_streams.php

...
public function up()
    {
        Schema::table('streams', function (Blueprint $table) {
            $table->unique('stream_name', 'streams_stream_name_unique');
        });
    }
...

edit.php

public function addStream($id) {
    ...
    $validatedData = Validator::make(
       ['stream_name' => $this->stream_name],
       ['stream_name' => 'required|unique:streams'],
       ['required' => 'Stream Name is Required'],
    )->validate();
    
    $stream = Stream::create([
       'stream_name' => ucwords($this->stream_name)
    ]);
    ...

标签： phplaravel

解决方案

你可以做这样的事情，尽管对于一张大桌子来说可能需要一段时间：

...
public function up()
{
    DB::unprepared("
        CREATE TABLE tmp_streams SELECT * FROM streams;
        TRUNCATE TABLE streams;
        ALTER TABLE streams ADD UNIQUE INDEX streams_stream_name_unique(stream_name);
        INSERT IGNORE INTO streams SELECT * from tmp_streams;
        DROP TABLE tmp_streams;
    ");
}
...

如果您关心保留哪些字段或行，则可以使用有序 SELECT 和/或 ON DUPLICATE KEY UPDATE 重写 INSERT。

如果您碰巧使用的是 5.7 之前的 MySQL 版本并且不在乎保留哪一行，那就更简单了：

ALTER IGNORE TABLE streams ADD UNIQUE INDEX streams_stream_name_unique(stream_name);

根据ALTER TABLE 语法文档：

IGNORE 是标准 SQL 的 MySQL 扩展。如果新表中的唯一键有重复项或启用严格模式时出现警告，它会控制 ALTER TABLE 的工作方式。如果未指定 IGNORE，则在发生重复键错误时中止并回滚副本。如果指定了 IGNORE，则仅使用在唯一键上有重复的行中的一行。其他冲突的行被删除。不正确的值将被截断为最接近的匹配可接受值。

从 MySQL 5.6.17 开始，不推荐使用 IGNORE 子句，并且它的使用会产生警告。IGNORE 在 MySQL 5.7 中被删除。

推荐阅读