Laravel Schema Builder | Unique, Case Sensitive Column

Question

I'm using Laravel's schema builder with mysql to make a unique column. But when I use the unique method it is case insensitive. I need it to be case sensitive. How can I do that?

Schema:

Schema::create('item', function (Blueprint $table) {
    $table->increments('id');
    $table->string('key')->unique();
    $table->timestamps();
});

First entry into database:

$i = new Item;
$i->key = "Random_Key";
$i->save();

Second entry into database (returns duplicate entry error):

$i = new Item;
$i->key = "random_key";
$i->save();

Sisco

Answer 1

You need to use character sets and collations for specifying case sensitive columns in mySQL

Laravel has the collate and charset column modifiers in mySQL for this purpose

So, you may use something like: $table->string('key')->charset('utf8')->collate('utf8_cs')->unique()

As the OP stated, here's what worked for him:

$cs = $table->string('key')->unique();

$cs->collation = 'utf8_bin';

Docker

I tried utf8_bin as the collatation, but when I migrated the tables it won't change the collation. I have to go in to the database and manually change the collation. I found a solution to this though. All you have to do is something like this... $cs = $table->string('key')->unique(); then you need to do the following... $cs->collation = 'utf8_bin'; – Sisco

Glad you found a solution. I'll edit my answer to include what worked for you – Docker

This helped me, expecially the ->collation('utf8_bin') at the end (without it it does not work) – Linstock

Answer 2

How about setting the type of the column VARBINARY?

DB::statement('ALTER TABLE item MODIFY id VARBINARY(64);');

Beverlybevers

If you have a question please create new one. This section is only to provide answers – Pelagia

Answer 3

I had the same profile with Laravel 11, but none of the above worked. I created a new migration and added a manual column change.

public function up()
{
    DB::statement('ALTER TABLE unit_types MODIFY name VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE');
}

public function down()
{
    DB::statement('ALTER TABLE unit_types MODIFY name VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci UNIQUE');
}

Magdau