5

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();
wizardzeb
  • 1,546
  • 2
  • 14
  • 30

2 Answers2

10

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';
Paras
  • 9,258
  • 31
  • 55
  • 3
    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';` – wizardzeb Feb 04 '17 at 20:30
  • 1
    Glad you found a solution. I'll edit my answer to include what worked for you – Paras Feb 05 '17 at 18:04
  • This helped me, expecially the ->collation('utf8_bin') at the end (without it it does not work) – Masiorama Apr 15 '22 at 08:37
0

How about setting the type of the column VARBINARY?

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

bgazsi
  • 141
  • 8
  • If you have a question please create new one. This section is only to provide answers – DjSh Jun 26 '19 at 19:21