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);
id
, give the framework an amateurish feeling around the edges. Am I ranting? :-(
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']);
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']);
});
->unique()
.
->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.
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');
});
}
DB::statement("ALTER TABLE `project_majr_actvities`
ADD UNIQUE `unique_index`(`activity_sr_no`, `project_id`)");
Success story sharing
array()
. Because I tried without array and it did not work. can I give constraint name while running the composite key through Schema builder ?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');
dropUnique()
that you passed tounique()
to guarantee the constraint/index name is the same.