We recently encountered a problem where we set the item_name field in Item doctype as unique. To our surprise, the constraint wasn’t working.
After investigating, I found that making a field unique is done with the SQL command:
...
modify_column_query.append(
f"ADD UNIQUE INDEX IF NOT EXISTS {col.fieldname} (`{col.fieldname}`)"
)
...
I queried the database for the indexes in tabItem and found that there was already an index on item_name. The framework has already added an index to item_name in the doctype definition.
Consequently, even though unique was set in the docfield, it wasn’t actually set because there was already an index set by the framework and the database server just ignored the command without updating the Meta.
This seems to be an edge case but I think it exposes a problematic scenario where the framework is telling you that a field is unique while it actually isn’t. In our case, we noticed it from Customize Form but I’m pretty sure it will happen in Doctype although it’s easier to fix if you have access to Doctype and actually know the problem.
I’m not sure if to consider this a bug but it is definitely undesirable. Should the framework make it possible to change the index property in Customize Form? Should the framework remove the IF NOT EXISTS and allow the program to throw an error? Should the framework try to figure out this edge case and try to replace the index? Should the framework work as is while updating the Customize Form view to show unique as unchecked? etc