Laravel / MySql Unique string based on id?
I have the following migration to create a MySQL database table
public function up()
{
Schema::create('project_auth_keys', function (Blueprint $table) {
$table->increments('id');
$table->integer('project_id')->unsigned();
$table->string('key', 800);
$table->string('first_name', 80);
$table->string('last_name', 80);
$table->timestamps();
// link project id with real project id
$table->foreign('project_id')->references('id')->on('projects');
});
}
Now what I want to accomplish is to make the "key" unique ... However, I know I can do this by simply changing
$table->string('key', 800)->unique();
But I don't want it to be unique across the whole table, I want to make it unique based on the project_id.
For example, an entry with a project_id of 1 and an entry with a project_id of 2 may have the same key, but there cannot be two identical keys in the same project_id.
Is this something I can do in MySQL, or do I need to do this usually in my controller? I can do this in my controller no problem, but I would rather do it in my database if possible.
EDIT
Tried adding
$table->primary(array('project_id', 'key'));
Added it right below $ table-> primary (array ('project_id', 'key'));
However, after doing this, I get an error when I try to migrate.
source to share
Something like that?
$table->primary(array('project_id', 'key'));
You can find the same in the documentation here .
Solution 1: Since you already have a specific increment column, it accepts the same as the default primary key.
To use a composite primary key, you can either drop this column or calculate this column differently, otherwise it will cause a duplicate primary key.
In addition, the limitation on the length of the primary key is "767", and therefore you should reduce the key length further than possible.
I just tried the following and it works:
Schema::create('test', function(Blueprint $table)
{
$table->integer('project_id')->unsigned();
$table->string('key', 100); // make sure the key length is within sql standards(767), "800" is not acceptable for primary key
$table->string('first_name', 80);
$table->string('last_name', 80);
$table->timestamps();
// Add primary
$table->primary( array( 'key', 'project_id' ) );
});
Solution 2: You can simply do the validation in the controller, which gives you more flexibility, I believe, and has the same structure, with a single identifier as the primary key. However, I'm not very sure about the performance. You will need to check this.
Hope it helps.
source to share