Possible to set field requirements at database level?

Kevin Wasie

Is it possible to set field requirements inside of the database, instead of in an API? A way more advanced version of unique index's.

For instance, if we only allow employees to work on projects that their supervisor is the manager of...

if I want to ensure that whenever a new employee is added to a project in the database, the database checks the project manager. The database then does not allow it if it is not their supervisor id and throws an error and an error message.

I get that this all can be done in the API. But, this requirement might be something that is a requirement for those fields, and error checking it in every API endpoint that deals with adding a new user to a project is a pain.

Ray Deck

Kevin, I would do this with custom functions. By wrapping the business logic of setting a record into a custom function, you get to introduce this kind of safety and get far more readable endpoint code as well. So you could make "add_project_to_employee" which validates the supervisor relationship before running the add record.

This functionalization approach is essentially how I would do it in a "pro-code" world too. One makes a data controller that the endpoint code calls rather than executing direct database statements from the peripheral (e.g. endpoint) context. 

Kevin Wasie

 That makes sense. I was hoping for an easier solution. 

It would be awesome if you could simply right click the column in the DB and define rules and the system would automatically overlay that. 

Even things like a conditional in the same table  "If field_a is true (bool), ensure that field_b is >0" takes so much work to enforce that. I wanted a simpler solution lol 🙂

I wish there was an easier way to define global field rules.