schema builder laravel migrations unique on two columns

Question

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);

Possible duplicate of [Laravel 4: making a combination of values/columns unique](http://stackoverflow.com/questions/16990723/laravel-4-making-a-combination-of-values-columns-unique) — Organic Advocate, Feb 28 '17 at 19:11
This level of detail is sadly missing from the [Laravel docs](https://laravel.com/docs/5.5/migrations). 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? :-( — cartbeforehorse, Dec 04 '17 at 12:45

score 331 · Answer 1 · edited Nov 13 '17 at 15:32

331

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']);

edited Nov 13 '17 at 15:32

Chuck Le Butt

43,669
58
179
268

answered Nov 19 '13 at 15:07

Collin James

7,662
2
25
36

1

+1 thanks for this...not sure how I missed it in the documentation. I must be blind :P – OACDesigns Nov 29 '13 at 20:17
1

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 – Ciprian Mocanu Feb 02 '16 at 09:04
2

+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 ? – Pankaj Feb 17 '16 at 20:28
Yeah, that's the second param – Collin James Feb 17 '16 at 20:29
8

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 May 05 '17 at 13:26
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes I created the constraint on 4 columns (with 4 it fiail with the error above, with 3 it work correctly). And i set a short index name. Any idea that can help? – Mohamed Allal Nov 21 '18 at 14:39
I answer myself, as too a lot of answer around such a problem are already answered! My columns had typically a long lenght. all VARCHAR(255). I changed one to 45 and it did the trick. – Mohamed Allal Nov 21 '18 at 14:43
Which validation rule should be used to validate this? – MohNj Jul 02 '20 at 14:09
@MohNj validation rule will be [unique](https://laravel.com/docs/8.x/validation#rule-unique) – Akshay Gaonkar Oct 22 '20 at 07:02

score 23 · Answer 2 · edited Apr 26 '21 at 13:43

23

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']);
    });

edited Apr 26 '21 at 13:43

lewis4u

11,579
13
79
118

answered Nov 14 '15 at 13:06

İsmail Atkurt

1,078
1
9
16

5

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()`. – okdewit Nov 21 '16 at 10:13
3

@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. – Jeff Puckett Mar 01 '17 at 22:26
2

Also notice that composite primary keys are generally frowned upon by the Laravel developers, and they are not supported by Eloquent - see https://github.com/laravel/framework/issues/5355 – andrechalom Oct 06 '17 at 15:35

score -2 · Answer 3 · edited Mar 30 '20 at 13:01

-2

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

edited Mar 30 '20 at 13:01

Tony

8,904
3
41
67

answered Jan 17 '20 at 09:07

Rajendra Rajput

63
6

a verbal explanation would be a helpful addition to your answer – con Mar 30 '20 at 19:55
3

This is the nuclear option – Rolf Feb 28 '21 at 22:35

schema builder laravel migrations unique on two columns

3 Answers3

Linked