Add unique constraints to nullable & JSON columns in Laravel

A few weeks back, I tweeted a quick tip on adding unique constraints to JSON columns in Laravel:

Need to add a unique constraint to a #JSON column in #laravel? Use a virtual column to do it easily.

Note: haven't forgotten about the follow-up JSON column types article on https://t.co/iatkxsjQHX , life has been incredibly busy & we're finally getting settled into our new home pic.twitter.com/VxvscQZ4jQ

— Jani Gyllenberg (@jani_gyllenberg) June 7, 2019

… which translates to:

ALTER TABLE barcodes ADD serial_number VARCHAR(255) AS (JSON_UNQUOTE(unit->"$.serial_number"));
CREATE UNIQUE INDEX unique_inventoriable ON barcodes(serial_number, inventory_model);

However, the concept can also be applied to nullable columns (as MySQL ignores columns with null values from unique constraints). An example would be if you wanted to have a constraint that took into account the state of a Model with soft-deletes:

$table->boolean('was_deleted')->virtualAs('IF(`deleted_at` IS NOT NULL, TRUE, FALSE)');

… or in SQL:

ALTER TABLE barcodes ADD was_deleted BOOLEAN AS (IF(`deleted_at` IS NOT NULL, TRUE, FALSE));