Cant add different case adid to database because of unique constraint-CodePudding

Before I state the issue I'll mention I'm using PHP and laravel 9, and my database is mysql with the following charset and collation: 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',

I am working with ADIDs and a table called devices where the device->adid has the unique constraint. I know this sounds unnecessary, but for reasons I need the unique to be case sensitive so that I can store the ADID uppercase and lowercase.

I'm trying to make it so I can create a device like this:

Devices::create(['adid' => '12341234-1234-1234-1234-NOTAREALADID]);

AND then make a second device with this adid:

Devices::create(['adid' => '12341234-1234-1234-1234-notarealadid]);

Currently, if I was to try this, the first device would be added to the database properly and the second one would be denied because of the unique constraint. Anyone know how I can get around this?

FYI if it helps, the column in the DB isn't case sensitive, its a string and I can push upper or lower case characters to it, I am only stopped if I try to push anything that already exists that would match case-insensitive.

If I'm missing any important information please let me know and I'll add it, this is my first post!

CodePudding user response：

Case-sensitivity is determined by the collation defined for the column. A unique index created for that column will follow the collation of the column.

In MySQL 8.0, the case-sensitive collations for utf8mb4 are utf8mb4_bin and utf8mb4_0900_as_cs ("cs" means "case-sensitive" in the collation naming convention).

In MySQL 5.x, utf8mb4_bin is the only choice for a case-sensitive collation for utf8bm4.

You don't have to change the collation for your whole database. You can change it for just the specific column or columns you want to be treated this way.

CodePudding user response：

I was able to fix it by changing the collation on this single column from utf8mb4_unicode_ci to utf8mb4_bin using a migration like this:

DB::statement("ALTER TABLE devices MODIFY adid VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin");

Thanks Jay Terrill for a url that helped me along the way, here was the final answer that helped me out.