John Davidson

Message:

I am using Laravel 8 and I have this table:

Schema::create('users', function (Blueprint $table) {

    $table->id();

    $table->string('username')->nullable()->unique();

});

In MySQL the username is caseinsensitive unique. This means I cannot have two rows like this:

id | username

1  | MAX

2  | max

However, in SQLite, it its casesensitive unique, meaning the above example would be possible.

I found at https://stackoverflow.com/a/20914992 how the raw SQLITE command is for this. However, I don't want to use a raw SQL command, because I would like that my migrations work for both, SQLite and MySQL.

I found a similar question at Laravel Schema Builder | Unique, Case Sensitive Column which asks for the opposite and suggests to use the collation migration-modifier to set to an case-sensitive collocation. Unfortunately, the collation does not seem to work for SQLite on Laravel but only for (MySQL/PostgreSQL/SQL Server).

How can I adjust the migration, so that the column is unqiue case insensitive for MySQL and SQLite?

---

My Answer:
To create a unique case insensitive column for both MySQL and SQLite in Laravel, you can use the following approach:

php
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('username')->nullable();

if (config('database.default') == 'mysql') {
$table->unique('username');
} else {
$table->unique('username', 'unique_username_case_insensitive');
DB::statement('CREATE UNIQUE INDEX unique_username_case_insensitive ON users (LOWER(username))');
}
});


In this code snippet, we first create the 'username' column as a regular string column. Then, we check the database driver being used (MySQL or SQLite). If MySQL is being used, we simply create a unique index on the 'username' column. If SQLite is being used, we create a unique index on the lowercase version of the 'username' column using a raw SQL statement.

This way, the 'username' column will be unique case insensitive for both MySQL and SQLite databases.