How to use unique constraint effectively along with DeletedAt

[dayadev]

Using a unique constraint on a column in a table along with DeletedAt

I have a scenario where I would like to add a unique constraint on a name column and also use DeletedAt with mysql but looks like mysql doesnt allow custom indexes like a index on name and DeleteAt which makes it a bit not straight forward since unique index on just the name fails when a row with same name is previously deleted

Expected answer

Is there an in-built way to handle unique constraints for mysql using gorm that allows me to add a same name when the previous one is soft deleted

[stavros-k]

Not a DB expert but I stumbled on your question, and looking at the gorm docs and SQL docs in general,
It should be doable, but adding a constrain in multiple fields, using Unique Indexes

eg (not really a valid sql, just what I gathered around)
CREATE UNIQUE INDEX a_unique_constrain ON your_table (name);

If I understand it correctly those 2 are considered unique and you can have them

id,name,deleted_at
1,joe,2021-01-01 10:00:00
2,joe,null

But this will also "pass"
id,name,deleted_at
1,joe,2021-01-01 10:00:00
2,joe,2022-02-02 12:00:00

This probably can be fixed with this
CREATE UNIQUE INDEX a_unique_constrain ON your_table (name) WHERE deleted_at IS NOT NULL;

---

That being said, I have not tested any of it, but will probably do "soon"

[dayadev]

Not a DB expert but I stumbled on your question, and looking at the gorm docs and SQL docs in general, It should be doable, but adding a constrain in multiple fields, using Unique Indexes

eg (not really a valid sql, just what I gathered around) CREATE UNIQUE INDEX a_unique_constrain ON your_table (name);

If I understand it correctly those 2 are considered unique and you can have them

id,name,deleted_at 1,joe,2021-01-01 10:00:00 2,joe,null

But this will also "pass" id,name,deleted_at 1,joe,2021-01-01 10:00:00 2,joe,2022-02-02 12:00:00

This probably can be fixed with this CREATE UNIQUE INDEX a_unique_constrain ON your_table (name) WHERE deleted_at IS NOT NULL;

That being said, I have not tested any of it, but will probably do "soon"

@stavros-k thanks for responding but I dont think mysql supports custom indexes. I have tried this with postgres which works but not with mysql.

[stavros-k]

Looks like it does, but not sure if it supports the WHERE clause part, or it needs some other syntax
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique