Question about unique key

[anym0re]

Shouldn't we use it like No.1 to keep the unique function intact?

I don't know why you explain like the link below and No.2

https://www.singlestore.com/blog/laravel-singlestoredb-quickstart-guide/

Is there a reason to use index instead of unique?
Is there anything I didn't understand or missed?

If you know why, I want you to explain. Thank you

No. 1

Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('name');
      $table->string('email');
      $table->timestamp('email_verified_at')->nullable();
      $table->string('password');
      $table->rememberToken();
      $table->timestamps();

      $table->unique(['id', 'email']);
});

No.2

Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('name');
      $table->string('email')->index();
      $table->timestamp('email_verified_at')->nullable();
      $table->string('password');
      $table->rememberToken();
      $table->timestamps();
});

[carlsverre]

Thanks for the great question @anym0re!

The reason we can't have a unique index on the "email" column is that SingleStoreDB is a sharded database. This means that for us to know whether or not a particular email is unique would require storing all of the rows with the same email value on the same partition. But, in this case, we also want a primary key on the ID column - which has a similar issue. So, unfortunately there is no way to have a unique index on two different columns in the same table.

However! As you point out, you could put a unique index on (id, email) as that index starts with the primary key (which also happens to be the shard key for the table). While this does technical work, it doesn't actually do anything useful. Since ID is always unique, having a unique index on (id, email) would not accomplish the goal of detecting when two users have the same email. Consider the following example:

User 1 has email [email protected]
User 2 has email [email protected]

In this case, the index would be:
(1, [email protected])
(2, [email protected])

As you can see, all of the tuples in this index are unique from one another.

I hope my explanation makes sense. To work around this issue, we recommend performing unique checks on secondary columns in your application rather than in the database. You can use a multi-statement transaction to check uniqueness and insert the user atomically, rolling back the transaction if the email is already used.