How to enforce uniqueness constraints within relations?

Dev Support
1

So I have the need to do some variation of the following:

  • In my schema, I have Users, Teams and Projects
  • Users have a relation to a Team
  • Projects have a relation to a Team

To make the system work, I want:

  1. Each Team to have a unique name: easy, just make the team.name unique
  2. Each Project to have a name that is unique within its Team

What is the recommended way to achieve the 2nd condition in a robust way? I wouldn’t want to manually check it upon inserts and updates.

I tried looking into calculated fields and indexes but couldn’t find a way to do this that made sense. It’s a pretty common use case though, I’d say, so I feel like I’m missing something rather obvious.

2

Hello! I’ve got it, but I’m not sure I can come up with the native data builder solution right away. Do you consider using custom triggers to validate a project’s name uniqueness within a Team?

1 Like
3

I’ll look into that, looks like that might be a good solution. I had some issues importing modules in custom functions so I’ll see how it goes.

4

I can confirm that this works and the end result is the functionally that I want. Maybe even worth spelling out the use case in documentation.

It can be quite laborious to manually write the code, though. I have to write the code for create and update slightly differently, query the DB differently and carefully write the triggers in a way that manages the input parameters correctly and returns the correct type of object.

If I do more constraint checking for a lot of different types of objects, it will get a little bit heavy to maintain. I do appreciate the flexibility of the triggers a lot, it’s a good pattern - there’s no blocker with this solution, and I can develop the backend-side codebase further to make this more scalable. There’s some room for making common constraints easier to implement on the platform though.

1 Like
5

Thanks a lot for the feedback! Happy that you’ve managed to make it)
Yeah, you’re right, I guess it would be cool to have at least a knowledge base with implementations of use cases like yours and to think about simplifying, providing UI features for these.

6

Glad you figured out a working solution. Totally agree that this is a type of validation that would be valuable to expose in the console without code. That said, it’s accomplishable using a custom function and calculated field.

Please share your solution if you are open to doing so!

7

Now that you mentioned it, I think enforcing uniqueness on a calculated field, which concatenates the ID of the related object with the unique field value would work great. Wouldn’t need custom code for that, or new validation options. But it doesn’t seem to be possible to use field values from relations.

I really like calculated fields btw. They’re essential when writing reactive client-side code, so leveraging that pattern more in the backend would be very powerful.

I added the rough solution here in this gist: https://gist.github.com/jerryjappinen/0982596512e6670196029ee523491232

I don’t mind writing the triggers too much, but it will add up in a larger project, and it’s easy to break the code. Maybe a dedicated way of writing similar stuff as constraints with an easier way to test them, or doing this via permissions (but there I can’t set custom insert permissions).

8

Good to know! The calculated fields can only pull fields from its own record and not related records. I need to check with the team to see whether enabling relations is possible, as these are native MySQL expressions that are being written.