Create unique index on non-unique data
The title is a clickbait. You can’t do that unless some data migrations were done.
Let’s say we have a font variant table with below schema:
CREATE TABLE font_variants ( family VARCHAR(255) NOT NULL, weight INT NOT NULL, style VARCHAR(255) NOT NULL, user_id UUID NOT NULL );
All is good until after a while, we realize that a variant is unique by
style) and we should enforce that on database level.
The problem is old data were filled up with not unique rows, when we try to
create an unique index like this, it will fail:
CREATE UNIQUE INDEX font_variants_family_weight_style_idx ON font_variants(user_id, family, weight, style);
We can’t make those rows unique unless we change the row data. Fortunately, browsers are handling font-weight gracefully and we can do the migration by changing the weight without huge impact on font display, even if you using the weight on database for rendering font CSS directly.
Let’s increase weight by the duplication number on each duplicated row:
UPDATE font_variants SET weight = weight + rn - 1 FROM (SELECT id, row_number() OVER (PARTITION BY user_id, family, weight, style) rn FROM font_variants) fv WHERE font_variants.id = fv.id;
We’re good to do the unique index creation now.