Add field option for "unique or null"

[thgh]

Sometimes a piece of data can be optional, but if it is filled in, it must be unique.

Unique and required

{
  unique: true,
  required: true
}

Unique and not required: null can exist once

{
  unique: true,
  required: false
}

Unique or null: null can exist multiple times

{
  uniqueOrNull: true,
  required: false
}

[hades200082]

I'm not sure what use-cases exist for the "null can exist once" option.

It would seem to me that if unique is true and required is false it should imply "unique or null".

[thgh]

Got the same gut feeling, but did actually encounter a usecase for "null can exist once" so I would not make it impossible. Also, it is the default behaviour for unique in postgres.

[DanRibbens]

This is valid, I agree!

In Mongo we add the sparse option to the index as in the case of localized fields. I noticed the other day that we have no way of allowing sparse in the config which is exactly what we need for this requirement.

[thgh]

About localized: it seems that if I enable/disable localized, the sparse index stays whatever it was when the field was added. Is this intended behaviour? It caused a bug in a production deployment, did not expect it.

[DanRibbens]

Sorry this caused an issue for you @thgh. It has always worked this way. Payload will not remove or change existing indexes from changing the config. As you found out, you have to manually remove them to be re-created automatically when Payload starts again.

[hades200082]

Instead of

{
  uniqueOrNull: true,
  required: false
}

Might be better to have unique typed as boolean | { allowNulls: boolean } and used like this:

{
  unique: {
    allowNulls: true
  },
  required: false
}

This retains backwards compatibility with current versions, but adds the new feature without needing a new property.

A later major version could remove the backwards compact and switch it to false | { allowNulls: boolean }, the implication bing that if an object is passed then unique is true, and the allowNulls must then be set explicitly.

[XGZepto]

It would be sooooo useful to have tbh; is it anywhere on the roadmap yet?

[ToneseekerMusical]

This is definitely needed. I've hit a roadblock while working on my website because of this.