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

Spiegleman

Possible duplicate of Laravel 4: making a combination of values/columns unique – Segarra

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? :-( – Dragline

Answer 1

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

Kukri

+1 thanks for this...not sure how I missed it in the documentation. I must be blind :P – Torrens

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 – Fearsome

+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 ? – Olodort

Yeah, that's the second param – Kukri

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'); – Turn

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? – Aimo

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. – Aimo

Which validation rule should be used to validate this? – Irvingirwin

@Irvingirwin validation rule will be unique – Xanthate

@Turn You can also pass the same array to dropUnique() that you passed to unique() to guarantee the constraint/index name is the same. – Burglar

Answer 2

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

Neighboring

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(). – Slotnick

@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. – Omalley

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 – Advertent

@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. – Rog

Answer 3

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

Pella

Answer 4

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

Adscription

Answer 5

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.

Anthropoid

Answer 6

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

Selaginella

a verbal explanation would be a helpful addition to your answer – Institutional