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.

enter image description here

+3


source to share


1 answer


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.

+2


source







All Articles