[SOLVED] Create Mutli-field Unique Constraint

[pmacmannis]

I have searched the forum and have not seen any indication this request has been made before. Apologies if I missed it.

I need to be able to make some kind of constraint on a schema that essentially considers 2 or more fields as unique together, like a composite key.

I’m less concerned about whether I need to do this as a rule, a script or as a toggleable setting in the Squidex UI (like say, making a content section unique as a way to group fields).

Example:
F1 and F2 are part of my unique group.

| Row     | F1      | F2      | Allowed |
| ------- | ------- | ------- | ------- |
|  1      | Val1    | Val2    |  Y      |
|  2      | Val1    | Val3    |  Y      |
|  3      | Val2    | Val2    |  Y      |
|  4      | Val2    | Val3    |  Y      |
|  5      | Val1    | Val3    |  N      |

The 5th insert above conflicts with the composite unique value of F1 and F2. F1 is allowed to have repeating values, as is F2 so long as the pair do not combine to break that.

[Sebastian]

In general unique fields are no guarantee. It is only a validation before a content is created:

What you can do:

1. Create a script

ctx.data.unique = { iv: ctx.data.F1.iv + ctx.data.F2.iv ... }
replace();

2. Mark this fiel das unique.

3. Disable the field with a general field setting or hide it with a field rule.

[pmacmannis]

Thanks for the quick reply.

The validation is really all I am looking for. If it gets past that, we can handle errors down the line.

I’ll give that a go.

[pmacmannis]

Just as a follow up for anyone using this technique. For the most part (sufficient for now), the solution works for my use case.

Additional Step:
I also needed to edit the schema, under the “More” tab and check “Validate when publishing” checkbox (saw this in another question similar to this).

This prevents pressing the “Save and Publish” with a duplicate value. But if you save it as a draft and then manually shift the current version from Draft to Publish (on the workflow right nav), it does NOT validate the unique fields.

@Sebastian Is that a bug or is that by design?

[Sebastian]

@pmacmannis

@Sebastian Is that a bug or is that by design?

What exactly? Why would you validate again? If another content has been created in the meantime, this should have thrown an error.

[pmacmannis]

I guess my question is that I set the schema to Validate on publish, but when I publish via the workflow nav, it does not run the validation.

I would think that by setting it to validate on publish, it would run validate regardless of which UI path I took to publish.

EDIT: The way I tested this was to try and create a content that would trigger the validation (the duplicate already exists and is published). It does not validate when saving as a draft (makes sense, I told it to validate on publish). To me, I see that as a gap in the “Validate when publishing” mechanism.

[Sebastian]

Validate on Publish means “Also validate on publish”. But I have to find out, why it does not validate.

[Sebastian]

It was actually a bug. The frontend is using the bulk endpoint where “expensive” validations are turned off by default, but can be turned on. Expensive validations is everything, that involves a call to the database or some other resource.