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 436 · Answer 1 · edited Nov 13 '17 at 15:32

436

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

47,570
62
203
289

answered Nov 19 '13 at 15:07

Collin James

9,062
2
28
36

2

+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
2

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
13

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) – 9paradox Oct 22 '20 at 07:02
3

@Jonathan You can also pass the same array to `dropUnique()` that you passed to `unique()` to guarantee the constraint/index name is the same. – Joel Mellon Feb 17 '22 at 20:43

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

32

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

14,256
18
107
148

answered Nov 14 '15 at 13:06

İsmail Atkurt

1,360
1
12
17

11

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
4

@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
4

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
@ismail - Adding a primary key based on two columns may *technically* accomplish the desire to add a unique constraint, but does more than what was requested, and injects other problems. Composite primary keys are not well supported by the Laravel framework. Please consider updating this answer to point to Malki's answer below which adds the unique constraint as requested (as well as including the down function) and nothing more. – Kevin Foster Nov 28 '22 at 19:36

score 15 · Answer 3 · answered Jan 05 '22 at 10:54

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

score 1 · Answer 4 · answered May 05 '23 at 10:36

Just to add (for someone who got the same error as mine) that I got an error when I added unique and foreign key in the migration. If I commented on anyone then there would be no error but when I use both; I would get an error. I was getting crazy with what was happening behind the scene in Laravel. Even thought of using DB::statement.

Schema::create('tender_docs', function (Blueprint $table) {
        $table->id()->autoIncrement();
        $table->unsignedBigInteger('tenders_id');
        $table->unique(['tenders_id', 'file_name', 'file_extension'], 'tender_docs_unique')
        $table->foreign('tenders_id')->references('id')->on('tenders')->onUpdate('cascade')->onDelete('cascade');
    });

The solution was simple: combine the unique key and foreign key statements into one.

        $table->unique(['tenders_id', 'file_name', 'file_extension'], 'tender_docs_unique')
        ->foreign('tenders_id')->references('id')->on('tenders')->onUpdate('cascade')->onDelete('cascade');

score 0 · Answer 5 · answered May 22 '23 at 09:09

public function up()
{
    Schema::create('courses', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('curricula_id')->nullable();
        $table->string('course_names');
        $table->string('course_codes');
        $table->double('assessment_weights',8, 2);
        $table->double('assessment_pass_mark',8, 2);
        $table->double('payment_amount_birr',8, 2);
        $table->double('payment_amount_usd',8, 2);
        $table->integer('year')->nullable();
        $table->integer('credit')->nullable();
        $table->integer('term')->nullable();
        $table->longText('course_completion_criteria');
        $table->timestamps();
        $table->unique(['curricula_id','course_names']);
        $table->unique(['curricula_id','course_codes']);
        $table->foreign('curricula_id')->references('id')->on('curricula')->onDelete('cascade');
    });
}

NOTE

You can concatenate more than 2 columns of the table, but the character length of the unique key is not more than the normal column name length.

score -5 · Answer 6 · edited Mar 30 '20 at 13:01

-5

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

edited Mar 30 '20 at 13:01

Tony

9,672
3
47
75

answered Jan 17 '20 at 09:07

Rajendra Rajput

85
6

2

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

schema builder laravel migrations unique on two columns

6 Answers6

Linked