GORM Playground Link
https://github.com/go-gorm/playground/pull/570
Description
As per the documentation on https://gorm.io/docs/indexes.html#Index-Tag gorm supports conditional indexing using a where condition. But it seems to be not respected in MSSQL / TSQL
type User struct {
Abc string `gorm:"index:unique_abc,class:UNIQUE,where: abc IS NOT NULL"`
}
creating this field results in failure.
It tries alter table add column "abc" unique. But this fails.
Is there any work around where this can be created in single migration?
Comment From: jozephp
If I am right this is occuring not during table create but at table alter while adding a column with constraint is added?
Comment From: pradnyoday
If I am right this is occuring not during table create but at table alter while adding a column with constraint is added?
yes, @jozephp
it applies ALTER TABLE
Comment From: pradnyoday
Hi @jinzhu,
is there any way we can achieve this?
Comment From: pradnyoday
Hi @black-06 , @jinzhu
We have also observed that migration fails on this field as it queries
ALTER TABLE
this is not acceptable on unique indexes with condition it seems
Comment From: black-06
No, (although where is not implemented), the cause of this issue is not here. Please try these SQL manually:
ALTER TABLE "users" ADD "abc" nvarchar(256);
# Ignore where because the problem isn't there
CREATE UNIQUE INDEX "unique_abc" ON "users"("abc");
An error has occurred:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.users' and the index name 'unique_abc'. The duplicate key value is (<NULL>). (1505)
The statement has been terminated. (3621)
I'm not familiar with mssql so I'm not sure how it should be. But apparently it is not due to grom.
Comment From: pradnyoday
Hi @black-06,
Yeah we have pre-populated table, hence we would need the where clause to apply the constraint except for
Comment From: a4g4
I'm simply trying to create a partial unique index with deleted_at IS NULL. (pgsql)
I'm able to achieve this using following raw sql
CREATE UNIQUE INDEX idx_table_domain ON table (domain) WHERE deleted_at IS NULL;
I'm unable to do the same with gorm tag. I tried these tags:
gorm:"index:idx_table_domain,class:UNIQUE,where:deleted_at IS NULL"gorm:"uniqueIndex:idx_table_domain,where:deleted_at IS NULL"gorm:"index:idx_table_domain,unique,where:deleted_at IS NULL"
it produces constraint without condition: ALTER TABLE "table" ADD CONSTRAINT "idx_table_domain" UNIQUE("domain")
Comment From: wandering-tales
I found out that after upgrading GORM to v1.25.9 and Postgres SQL driver to v1.5.7 now it works using the format below:
type MyModel struct {
ID uint `gorm:"primarykey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index:;uniqueIndex:idx_my_model_my_field_deleted_at,priority:2,where:deleted_at IS NOT NULL"`
MyField string `gorm:"not null:idx_my_model_my_field,unique,where:deleted_at IS NULL;uniqueIndex:idx_my_model_my_field_deleted_at,where:deleted_at IS NOT NULL"`
}
A bit of explanation: for each partial unique index with condition including the null identity check of a column (another_column IS NULL), the accepted form to generate the proper migration is index:your_index_name,unique,where:another_column IS NULL, that is by using index with unique setting rather than directly uniqueIndex. The same requirement is not needed when the condition contains the negation of the identity check (another_column IS NOT NULL).