ChatGPT解决这个技术问题 Extra ChatGPT

schema builder laravel migrations unique on two columns

How can I set a unique constraints on two columns?

class MyModel extends Migration {
  public function up()
  {
    Schema::create('storage_trackers', function(Blueprint $table) {
      $table->increments('id');
      $table->string('mytext');
      $table->unsignedInteger('user_id');
      $table->engine = 'InnoDB';
      $table->unique('mytext', 'user_id');
    });
  }
}

MyMode::create(array('mytext' => 'test', 'user_id' => 1);
// this fails??
MyMode::create(array('mytext' => 'test', 'user_id' => 2);
This level of detail is sadly missing from the Laravel docs. It would be so easy to mention it in passing. Details like this and - for example - the fact that the framework always seems to assume that every table is going to have auto-incrementing id, give the framework an amateurish feeling around the edges. Am I ranting? :-(

C
Chuck Le Butt

The second param is to manually set the name of the unique index. Use an array as the first param to create a unique key across multiple columns.

$table->unique(array('mytext', 'user_id'));

or (a little neater)

$table->unique(['mytext', 'user_id']);

+1 thanks for this...not sure how I missed it in the documentation. I must be blind :P
I also somehow missed the fact the second param is to manually name the index and I had an automatically generated index name which was too long. Thank you, man! +1
+1 for array(). Because I tried without array and it did not work. can I give constraint name while running the composite key through Schema builder ?
The generated index names are in the format table_column1_column2...n_unique if anyone is unsure. Dropping the unique constraint would then be referencing that in $table->dropUnique('table_column1_column2...n_unique');
@Jonathan You can also pass the same array to dropUnique() that you passed to unique() to guarantee the constraint/index name is the same.
l
lewis4u

Simply you can use

$table->primary(['first', 'second']);

Reference: http://laravel.com/docs/master/migrations#creating-indexes

As an example:

    Schema::create('posts_tags', function (Blueprint $table) {

        $table->integer('post_id')->unsigned();
        $table->integer('tag_id')->unsigned();

        $table->foreign('post_id')->references('id')->on('posts');
        $table->foreign('tag_id')->references('id')->on('tags');

        $table->primary(['post_id', 'tag_id']);
    });

This does not guarantee uniqueness though, it just adds a composite index. Usually, you do not want the same tag twice on the the same post, so for this use case it's better to use ->unique().
@Fx32 this does guarantee uniqueness because it creates a composite primary key (which is indexed). However, I still agree that ->unique() is more appropriate in this specific question because 'mytext' would probably make for a bad key as would any VARCHAR or TEXT column. ->primary([]) would be great for ensuring uniqueness on integers such as pivot foreign keys.
Also notice that composite primary keys are generally frowned upon by the Laravel developers, and they are not supported by Eloquent - see github.com/laravel/framework/issues/5355
M
Malki Mohamed

If you have a default unique index with one column and you will change it with two columns, or create a new one with two columns, this script will do that for you:

public function up()
{
    Schema::table('user_plans', function (Blueprint $table) {
        $table->unique(["email", "plan_id"], 'user_plan_unique');
    });
}

public function down()
{
    Schema::table('user_plans', function (Blueprint $table) {
      $table->dropUnique('user_plan_unique');
    });
}

T
Tony
DB::statement("ALTER TABLE `project_majr_actvities`
               ADD UNIQUE `unique_index`(`activity_sr_no`, `project_id`)");

a verbal explanation would be a helpful addition to your answer